vba - Excel - Using COUNTIF/COUNTIFS across multiple sheets/same column

Question

I am trying to "COUNT" the number of a certain object in column I (in this instance) across multiple sheets. That value in column I is the result of a formula (if it matters). So far I have:

=COUNTIF('Page M904'!I:I,A13)+COUNTIF('Page M905'!I:I,A13)+COUNTIF('Page M906'!I:I,A13)

which works, but I am going to have 20 something pages to scan through. I would like to avoid having a page long formula.

I have tried

=COUNTIFS('Page M904:Page M906'!I:I,A13) and =COUNTIF('Page M904:Page M906'!I:I,A13)

but that results in a #VALUE.

And I think

=COUNTIFS('Page M904'!I:I,A14,'Page M905'!I:I,A14,'Page M906'!I:I,A14)

is a misapplication of the COUNTIFS because I get 0 when it should be 35.

I am trying to avoid using VBA for this application. But if has to be, then it has to be :) Thanks in advance for your time and help.

Answer 1

This could be solved without VBA by the following technique.

In this example I am counting all the threes (3) in the range A:A of the sheets Page M904, Page M905 and Page M906.

List all the sheet names in a single continuous range like in the following example. Here listed in the range D3:D5.

Then by having the lookup value in cell B2, the result can be found in cell B4 by using the following formula:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&D3:D5&"'!A:A"), B2))