COUNTIF across a number of worksheets
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 |
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 |
All times are GMT +1. The time now is 06:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com