CountIF Formula Help for Dashboard

tylermh

See screenshot. I want to create a graph on a dashboard that shows how many total rows on the sheet pertain to Medical, Fire, and/or Police (indicated by checkbox columns on each row). Will I need to create an Agency Name sub row for each Medical, Fire, and/or Police I want to be represented on the graph for the COUNTIF formula to work? How can I accomplish this? I've also played with using the Discipline column as a multi-select for the COUNTIF formula to use, but to no avail. Help?

Paul Newcome

Yes. When you create a cross sheet reference, the "Range" will have whatever it needs stored on the back-end. So if you selected the Medical column header to choose the entire column then it will DISPLAY as "Range1", but on the back-end will be stored as Medical:Medical. This means that the {Range} is the only range we need for the formula. We don't need to include a second range to specify where on the other sheet since we already selected that when creating the cross sheet reference.

Paul Newcome

You would need to have a column that lists out each thing that you want to count. Then you would use a COUNTIFS to count how many boxes are checked within the column.

=COUNTIFS(Fire:Fire, 1)

tylermh

Thanks, @Paul Newcome. Where would I insert the reference. This is where I'm at so far:

=COUNTIFS({Data Center_Data Submission Status Range 1}, Medical:Medical, 1))

Paul Newcome

Are you wanting this count on the same sheet or a separate sheet from the source data?

Exactly what is in {Data Center_Data Submission Status Range 1}?

tylermh

The latter. After a lot of trial and error, this ended up doing the trick^^

Paul Newcome

Yes. When you create a cross sheet reference, the "Range" will have whatever it needs stored on the back-end. So if you selected the Medical column header to choose the entire column then it will DISPLAY as "Range1", but on the back-end will be stored as Medical:Medical. This means that the {Range} is the only range we need for the formula. We don't need to include a second range to specify where on the other sheet since we already selected that when creating the cross sheet reference.