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!

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

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

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


"Peo Sjoblom" wrote:
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

Try this:

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


--
Biff
Microsoft Excel MVP


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


"Peo Sjoblom" wrote:
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

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

"T. Valko" wrote:

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


Biff
Microsoft Excel MVP

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&"'!"&CE LL("address",B7)),"x"))



--


Regards,


Peo Sjoblom

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

"T. Valko" wrote:

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


Biff
Microsoft Excel MVP

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

"T. Valko" wrote:

Try this:

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


--
Biff
Microsoft Excel MVP


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


"Peo Sjoblom" wrote:
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

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

--
Biff
Microsoft Excel MVP


"Brigette" wrote in message
...
After reading these messages I still could not get it to work. please help

"T. Valko" wrote:

Try this:

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


--
Biff
Microsoft Excel MVP


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


"Peo Sjoblom" wrote:
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

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

--
Biff
Microsoft Excel MVP


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

Ooops!

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

The formula should be:

=SUM(Smith:Jones!C7)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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")

--
Biff
Microsoft Excel MVP


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