COUNTIF across a number of worksheets

[Guest]

Hey

I am trying to count the number of times different initials across 10+
worksheets appear in a specific column.
Basically checking and counting the number of times PGB or RAB or AJC
appears in a specific range (the same range across all the worksheets)

I have managed to do this, however it requires producing some long-winded
formula.

Eg. To count the number of times PGB appears in the range C3-C36 across
several worksheets i have made the formula:

=(COUNTIF('Ward 3'!C3:C36, "PGB")+COUNTIF('Ward 5'!C3:C36,
"PGB")+COUNTIF('Ward 6'!C3:C36,"PGB")+COUNTIF('Ward
7'!C3:C36,"PGB")+COUNTIF('Ward 7'!C39:C72,"PGB")

To make this formula i basically copied and pasted the COUNTIF function
changing the ward number into the formula line.

Is there a better way of doing this?

Second question, I also need to do this for a number of other intials (at
least 20) and I dont really want to keep copying and pasting unless i have
to. Any suggestions?

Thanks

Rup

 

[Bob Phillips]

Put all the sheet names to be queried in cell range, C1:Cn (I will assume C3
here) and then use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C3&"'!C3:C36"),"PGB"))

Put the initials in a cell say M1:Mn, the formula in N1, and just drag-copy
the formula down.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)