CountIF Formula Help for Dashboard
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?
Best Answer
-
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.
Answers
-
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)
-
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))
-
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}?
-
The latter. After a lot of trial and error, this ended up doing the trick^^
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!