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.

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)

--
Biff
Microsoft Excel MVP


"dd" wrote in message
...
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.

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,


"dd" wrote:

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.

=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.

--
Biff
Microsoft Excel MVP


"Gary Mc" wrote in message
...
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,


"dd" wrote:

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.

Biff,
Thanks for the explanation - much appreciated!
GMc

"T. Valko" wrote:

=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.

--
Biff
Microsoft Excel MVP


"Gary Mc" wrote in message
...
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,


"dd" wrote:

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.

You're welcome!

--
Biff
Microsoft Excel MVP


"Gary Mc" wrote in message
...
Biff,
Thanks for the explanation - much appreciated!
GMc

"T. Valko" wrote:

=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.

--
Biff
Microsoft Excel MVP


"Gary Mc" wrote in message
...
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,


"dd" wrote:

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.