Sheet Summary - countif

Vickie Lim · 06/29/20

Hi there -

What is the best way to do a 'count' on a drop down field located at the "summary sheet"?

For example: I have a drop down field on summary sheet called "Alignment". It has drop down values such as cost reduction, revenue, risk/fraud. Each sheets has one of those alignment category.

I would like to have a summary report that shows all sheets by alignment count. For example, if I have 13 sheets, it might look like this:

cost reduction 4

revenue. 6

risk/fraud 3

I tried doing it on REPORTS, but it's showing a list of all project names (sheet) and it's corresponding

alignment, which is not what I had in mind :(

As always, thank you.

Newbie-on-training

Screen Shot 2020-06-29 at 3.46.33 PM.png

Chris Rea · 07/01/20

if all you're looking for is the pure number of items that match the specific dropdown:

Column1 (how many "revenue" items in list)

=countif(Alignment:Alignment, "Revenue")

Column2 (how many "cost reduction" items in list)

=countif(Alignment:Alignment, "cost reduction")

Column3 (how many "risk/fraud" items in list)

=countif(Alignment:Alignment, "risk/fraud")

I will say that this would likely work better as 3 items in a "sheet summary" formula, where you'd use the same "countif" formula as above it just wouldn't take up a whole column and wouldn't get lost in the rows when new data is added. you also could then display the information easily on a dashboard.

Vickie Lim · 07/01/20

Lol sorry @Chris Rea , I might not be explaining it well. Attached is a visual.

On each my 'Sheet Summary', I have a drop down field called 'Alignment'.

Let's say I have 10 sheets, what's the best way to get a breakdown count of 'Alignment' category?

Newbie-in-training.

Chris Rea · 07/01/20

@Vickie Lim I don't know if you saw my edit. The countif formulas I gave would do that for you. you could have one for each of the different alignment options and it would automatically update as rows are added/deleted from the sheet.

Vickie Lim · 07/01/20

@Chris Rea I did, but maybe I am not explaining it well. Sorry, let me try again.

The 'Alignment' field is not part of any columns in any of the 10 sheets, it's only available at the 'Sheet Summary' section (see previous attachment above). The user choose one of the drop down list to make it's selection for that sheet.

What's the best way to get a breakdown count of 'Alignment' category (like this, see below) if I have 10 sheets?

Screen Shot 2020-07-01 at 2.22.55 PM.png

Sheet Summary - countif

Answers

Help Article Resources

Categories