Jon asked in Computers & InternetSoftware · 8 years ago

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.

2 Answers

Relevance
  • Greg G
    Lv 7
    8 years ago
    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.

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

Still have questions? Get your answers by asking now.