Home/ Questions/Q 4406
Next
In Process
Computing Staff
  • 2
Asked: 2021-11-10T10:49:53+00:00 In:

Countif Across Multiple Worksheets

  • 2

hello I have 100 worksheets names 1 to 100 having same format having “YES” or “NO” in B2 of each worksheet I would like to count no of YES in B2 of each sheet in summary sheet.
I m using using Excel 2003 can I use this with Countif , and how?

Share

    1 Answer

    1. You can’t do it with just a =COUNTIF() function,
      you will need a bit more.

      Try this:

      First put your Sheet Names in cells AA1 through AA100,
      I’ve used only 9 here as an example, and you can use any column you like.

      So your data looks like:

            AA
1) Sheet2
2) Sheet3
3) Sheet4
4) Sheet5
5) Sheet6
6) Sheet7
7) Sheet8
8) Sheet9
9) Sheet10

      Now on Sheet1, cell A1, just enter the formula,

      =SUMPRODUCT(COUNTIF(INDIRECT(“”&AA1;:AA9&”!B2″),”Yes”))

      See how that works.

      MIKE

      http://www.skeptic.com/

      • 0