CoutIF Question

[dd]

I'm trying to count across 10 different worksheets. I've tried using the
formulas posted but I can't seem to get them to work. This is the formula
that I'm trying to use:

=COUNTIF(Appelopen:Sarahopen!H12,"x") and it keeps giving me a value
error.

If there are any x's, they are all in the same cell on each worksheet.
Meaning some may cells may not have an "x", they may be blank, but I just
want to know how many total x's not cells.

I hope this makes sense and thanks in advance.

 

[T. Valko]

COUNTIF doesn't directly work across many sheets. You can "trick" it into
doing so but it requires listing your sheet names.

The easiest way to do this would be to use a cell on each sheet with a
formula like this:

=--(H12="x")

This will return either a 1 or a 0. Then you can just do a sum formula like
this:

Assuming the cell on each sheet is AA1...

=SUM(Appelopen:Sarahopen!AA1)

 

[Gary Mc]

I was attempting the same thing recently and found this on the internet.
Personally I don't understand why it works but it did for me. Maybe one of
the MVP's can explain it. On the worksheet where you want your count to
display enter the names of your worksheets in cells H1 thru H7 (for my
example) and enter the formula below.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H7&"'!H12"),"x"))

Hope this helps,

 

[T. Valko]

=SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H7&"'!H12"),"x"))

The formula is essentially performing an array of COUNTIF's.

Let's assume the sheet names are Sheet1, Sheet2, Sheet3, Sheet4 etc.

=SUMPRODUCT(
COUNTIF(Sheet1!H12,"x");
COUNTIF(Sheet2!H12,"x");
COUNTIF(Sheet3!H12,"x");
COUNTIF(Sheet4!H12,"x")
)

That's not exactly how it does it but that should give you the basic idea of
what's happening.

The drawback is that you have to list the sheet names.

 

[Gary Mc]

Biff,
Thanks for the explanation - much appreciated!
GMc

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Biff,
Thanks for the explanation - much appreciated!
GMc