COUNTIF Across multiple worksheets for "x"

[janee]

I am familiar with COUNTIF - I am trying to count the number of times "x"
appears in the same cell of 7 different worksheets in the same Excel file.

=COUNTIF(Smith!B7:Jones!B7, "x")

I have tried every kind of variation I can think of to make this work. I
have tried SUMIF too. If I change the "x" value in the cell to a number "1"
- it then works with SUM.

Thanks for any insight. I am the IT person helping someone else!

 

[Peo Sjoblom]

You need to create a list with all the sheet names, like:

Smith
Jones
etc


Note that you need all sheet names, or else it won't work

then assume you put the sheet names in H1:H7

use

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

--


Regards,


Peo Sjoblom

 

[T. Valko]

COUNTIF won't work directly across many sheets.

If you "only" have 7 sheets I would opt for the less complicated route of
entering a formula on each sheet in the same cell like this:

=--(B7="x")

Then on your "summary" sheet:

=SUM(Smith:Jones!C7, "x")

 

[T. Valko]

Ooops!

Then on your "summary" sheet:
=SUM(Smith:Jones!C7, "x")

The formula should be:

=SUM(Smith:Jones!C7)

 

[janee]

THIS WORKED !! Thank you so much ! I fixed the formula where the H1-H7
remained constant when I copied the formula down the page. However, when I
copy the formula down the page, it is still looking at B7 in all of the
cells, instead of B8, B9, B10, as it goes downw the page. How can I make it
change the cells?

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$7&"'!B7"),"x"))

Thanks again
Janee


:
You need to create a list with all the sheet names, like:

Smith
Jones
etc

Note that you need all sheet names, or else it won't work

then assume you put the sheet names in H1:H7

use

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

Regards,

Peo Sjoblom

 

[T. Valko]

Try this:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$7&"'!B"&ROWS(A$1:A7)),"x"))

 

[janee]

This worked, too - thanks so much! I do not understand WHY it worked - can
you explain what it is looking at - what does the "'!B"&ROWS(A$1:A7)) mean??
Why are the rows A1:A7 there? There is no data in them.

Again - I really appreciate this.
JaneE

Try this:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$7&"'!B"&ROWS(A$1:A7)),"x"))


Biff
Microsoft Excel MVP

 

[Peo Sjoblom]

ROWS(A$1:A7))

is just there to create number 7, you can use any cell references as long as
it uses the first and 7th row and the first cell uses absolute reference
($B$1:B7) will work as well

so copied down it will return

7
8
9
and so on

and since it is concatenated with the Letter B inside the INDIRECT function
it will de-facto be

B7
B8
B9
and so on


this is another variety that will work the same way but it might be easier
to understand


=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$7&"'!"&CELL("address",B7)),"x"))



--


Regards,


Peo Sjoblom

 

[Brigette]

After reading these messages I still could not get it to work. please help

 

[T. Valko]

You'll need to explain what you're trying to do.