Use sheet names in column in COUNTIF formula?

  • Thread starter Thread starter Ed from AZ
  • Start date Start date
E

Ed from AZ

I have the names of my worksheets in Col A. I want to do a COUNTIF
for all these sheets in the same range on every sheet. Is there some
way I can incorporate the cell with the sheet name into the formula?
Something like:

Col A Col B
Sheet1 =COUNTIF('(ValueofThisSheet!A2)'!B2:B20,"*")
Sheet2
Sheet3

And then of course copy it down the column to pick up all the sheet
names?

Ed
 
Hi Ed,

you use the INDIRECT function to do this, like so:

=COUNTIF(INDIRECT("'"&A2&"'!B2:B20"),"item")

Then copy down.

Hope this helps.

Pete
 
Thanks, Pete! It worked great!
Ed


Hi Ed,

you use the INDIRECT function to do this, like so:

=COUNTIF(INDIRECT("'"&A2&"'!B2:B20"),"item")

Then copy down.

Hope this helps.

Pete






- Show quoted text -
Click to expand...
 
Back
Top