CountIF and Sights Auto-update question

tobrien3

Started using Sights this week, definitely a very cool feature! I've seen some things in the example gallery that I'd like to know how to do.

 

1. From the Sales Pipeline example in the Sights Gallery, I’d like to have Totals by Month for:

 

Status (Green, Yellow, Red RYG balls)

Stage, Rep and Segment (all drop down columns on the same sheet).

 

 

2. On the same Sight, I have the number of Projects, Revenue and some other important info and  for the next two months. I put those in using the metric widget. Any way we can have that auto-update (so it always shows those metrics for the three upcoming months)?

Travis

Hi Tobrien3,

 

There may need to be some preparation in your sheets for Sights. What some users will do is designate a section in their sheet where they calculate the metrics they want to see in their Sight.

 

Here is the formula you are looking for:

 

=COUNTIF(Status:Status, "Green")

 

Status is the name of the status column and Status:Status will count the entire column.

This will display a count of all the "Green" status.

 

This same formula can be modified to show a count of all your statuses, state, rep, etc - just choose the correct cell reference range and change the value it is looking for.

 

If you want to get the totals by month, you will want to use a COUNTIFS. There will need to be something in the row that identifies the month then use that in the COUNTIFS (for example a column with a MONTH() function that shows the month of the referenced date).

 

2. The Sight will pull in whatever data is in the linked cell. Its likely you can build formulas in the sheet to show metrics for a given timeframe (using the MONTH() function I mentioned above). You can use COUNTs, COUNTIFs, and SUMIFs to get your metrics while adhering to your date restrictions.

 

Let me know if you have trouble setting this up.