Search Entire 26 Page Excel Document for Two Criteria, Totals Number of Times it Occurs Throughout the File?

Question

I currently have a 26 page excel document that I am looking for a formula to search every page for two criteria and total the number of times the two criteria appear in the entire document. Say I want to search every column A for cells containing "F1,F2,F3,F4, etc." and every column C for a specific number ie. "L7350" How would I communicate this to excel? I am trying to compile the totals on the first page of the excel file.

Answer 1

Favorite Answer

Unfortunately, COUNTIF/COUNTIFS doesn't work across multiple sheets.

Here's what you can do. Anywhere on any sheet, or just add a new sheet (which you can then hide) and enter the name of each of your 26 sheets in a range and name that range something like SheetList

Now, wherever you want to count the F1's with L7350, enter:

=SUMPRODUCT(COUNTIFS(INDIRECT("'" & SheetList & "'!A:A"), "F1", INDIRECT("'" & SheetList & "'!C:C"), "L7350"))

Use a similar formula for F2, F3, F4, etc.

Answer 2

Use PIVOT REPORTING with filtering.......