COUNITFS w/Ranges from Multiple Sheets?

SmartsheetingPM

Hi all,

I'm trying to create a COUNTIFS formula that will look for the same criteria within multiple sheets (5+ sheets at a time). I have my criteria in one sheet, and want to look across other sheets for instances of that specific criteria, then tally up the instances in the original sheet w/the criteria. I'm setting a range per sheet that's limited to the column in which the specific criteria may appear.

Any ideas?

Thank you!

Genevieve P.

Hi @SmartsheetingPM

If you're looking at multiple sheets, you'll need to add together multiple, separate COUNTIFS, like so:

=COUNTIFS({Sheet 1 Column}, [Criteria]@row)+ COUNTIFS({Sheet 2 Column}, [Criteria]@row) + COUNTIFS({Sheet 3 Column}, [Criteria]@row)

Etc.

Does that make sense? See: COUNTIFS / Cross Sheet Formulas

Another alternative would be to create a Report across all these sheets. Ensure that the columns that have the criteria are all named the same. Then you can either Filter or GROUP by that criteria and use the SUMMARIZE function in the Report to COUNT by that column.

Cheers,

Genevieve

SmartsheetingPM

Thanks so much, Genevieve! I hadn't thought of multiple COUNTIFS and that does the trick (the report option isn't a solution here, as I'm trying to generate metrics that will be pulled into a dashboard).

Genevieve P.

Glad it works for you!