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?


Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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

  • Paul Newcome
    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
    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
    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
    tylermh ✭✭✭✭✭
    edited 06/25/21

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!