Reporting - Multiple data values in a call from multiple sheets

Charlene Pons

Hi Community:

I have a sheet that has a column with multiple text values in a one cell. I want to calculate how many times a text value is mentioned in a cell across multiple sheets. How do I do this?

Heather Duff

Hi @Charlene Pons ,

There may be a simpler way I don't know about, but I think you may have to use a formula that does a cross-sheet reference for each sheet, then sums them all up. For example:

=COUNTIF({source 1},CONTAINS([Text Column]@row,@cell))+COUNTIF({source 2},CONTAINS([Text Column]@row,@cell))+COUNTIF({source 3},CONTAINS([Text Column]@row,@cell))

Let me know if it works.

Best,

Heather

Charlene Pons

Thanks Heather, however I will have over 20 sheets I need to reference so I don't think this will work. But thank you.

Genevieve P.

Hi @Charlene Pons

You could create a Report which looks across all of your sheets.

If your column name is the same across these 20 sheets, then you can use this as a column to FILTER by, seeing if that column contains the text you're looking for.

Then you can use the SUMMARIZE function to quickly display a number, like so:

Will this work for you?

Here's more information on Reports and Summaries:

• Help Article on creating Reports
• Help Article on using Summary
• Webinar on using Reports to Summarize

Cheers,

Genevieve