# Countif over multiple sheets

#### davemorse

##### Board Regular
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

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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

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?)

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.

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

You could use

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

Replies
3
Views
563
Replies
4
Views
665
Replies
3
Views
206
Replies
3
Views
430
Replies
13
Views
585

1,219,811
Messages
6,150,363
Members
450,955
Latest member
rose8693

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back