COUNTIF across a number of worksheets
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)
"Rup1776" wrote in message
...
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
|