Countif over multiple sheets

[davemorse]

Hi all,
Im trying to to do a Countif over a number of sheets but for some reason my formula returns #value. Im trying to count the number of "Y"'s in cell B2 over each sheet.

Ive highlighted cell B2 and selected my 7 sheets from August 06 - Jan 07..and typed this is my formula

=COUNTIF('August 06:Jan 07'!B2, "y")

Am i doing something wrong?

Thanks

 

[MarkAndrews]

Why not do a countif formula for each sheet (on a seperate results worksheet), then cumulate these figures together?

EG

=COUNTIF(Sheet1!G:G,"no")
=COUNTIF(Sheet2!G:G,"no")
=COUNTIF(Sheet3!G:G,"no")
=COUNTIF(Sheet4!G:G,"no")
=COUNTIF(Sheet5!G:G,"no")
=COUNTIF(Sheet6!G:G,"no")

You could use

=COUNTIF(G:G,"no")+COUNTIF(Feb!G:G,"No") etc

 

[MarkAndrews]

Hi all,
Im trying to to do a Countif over a number of sheets but for some reason my formula returns #value. Im trying to count the number of "Y"'s in cell B2 over each sheet.

Ive highlighted cell B2 and selected my 7 sheets from August 06 - Jan 07..and typed this is my formula

=COUNTIF('August 06:Jan 07'!B2, "y")

Am i doing something wrong?

Thanks

Do you mean B:B on each sheet? or is there 'YYY' in B2 on each sheet? (do you want to count the number of times 'Y' appears in B2?)

 

[davemorse]

Hi,
I want to see if there is a "Y" in any of the sheets in cell B2 and then count up how many "Y"'s there are in all of the sheets.

 

[Gohar Shahab]

You can use formula like,,

=countif(range(August 06 sheet),"y")+countif(Range(Jan 07 sheet),"y")

As below for range you may have different ranges...

=COUNTIF('August 06'!A1:A8,"y")+COUNTIF('Jan 06'!A1:A8,"y")

Gohar
Using XL 2003

 

[MarkAndrews]

You could use

=COUNTIF(G:G,"no")+COUNTIF(Feb!G:G,"No") etc