Search Entire 26 Page Excel Document for Two Criteria, Totals Number of Times it Occurs Throughout the File?
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.
- Greg GLv 78 years agoFavorite 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.
- 8 years ago
Use PIVOT REPORTING with filtering.......