COUNTIF Across multiple worksheets for "x"

2193 views
Skip to first unread message

janee

unread,
Sep 16, 2008, 5:50:00 PM9/16/08
to
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

unread,
Sep 16, 2008, 6:01:12 PM9/16/08
to
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" <ja...@discussions.microsoft.com> wrote in message
news:93B81271-4F3C-42B1...@microsoft.com...

T. Valko

unread,
Sep 16, 2008, 6:03:17 PM9/16/08
to
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" <ja...@discussions.microsoft.com> wrote in message
news:93B81271-4F3C-42B1...@microsoft.com...

T. Valko

unread,
Sep 16, 2008, 6:07:48 PM9/16/08
to
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" <biffi...@comcast.net> wrote in message
news:OGS1IgEG...@TK2MSFTNGP04.phx.gbl...

janee

unread,
Sep 17, 2008, 11:56:21 AM9/17/08
to
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

T. Valko

unread,
Sep 17, 2008, 12:26:37 PM9/17/08
to
Try this:

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


--
Biff
Microsoft Excel MVP

"janee" <ja...@discussions.microsoft.com> wrote in message

news:03C44734-986F-4533...@microsoft.com...

janee

unread,
Sep 17, 2008, 3:01:03 PM9/17/08
to
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"&ROWS(A$1:A7)),"x"))


Biff
Microsoft Excel MVP

Peo Sjoblom

unread,
Sep 17, 2008, 3:34:19 PM9/17/08
to
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

"janee" <ja...@discussions.microsoft.com> wrote in message
news:DD261CFA-406D-4A1D...@microsoft.com...

Brigette

unread,
Aug 19, 2009, 5:18:01 PM8/19/09
to
After reading these messages I still could not get it to work. please help

T. Valko

unread,
Aug 19, 2009, 5:39:45 PM8/19/09
to
You'll need to explain what you're trying to do.

--
Biff
Microsoft Excel MVP


"Brigette" <Brig...@discussions.microsoft.com> wrote in message
news:B25A1793-3AF9-4E18...@microsoft.com...

Draven Shean

unread,
Oct 4, 2021, 3:07:54 AM10/4/21
to
Hello,

I have read all of the above messages and tried to apply these formulas onto a Google Sheet I am currently working on. Currently only 1 particular formula has proved to almost work.

In short, I have 1 Google Sheet Document with numerous sheets within it, and I am trying to make 1 of the sheets a "Master List" that is counting particular variables across numerous sheet tabs. For example:

I am trying to count how many times, "TX", appears across a range of cells in every sheet tab, and have that total counted number of occurrences appear on my master list.

I put, =SUMPRODUCT(COUNTIF(INDIRECT(""&April:October&"!B2:B50"),"TX")) , where "April" and "October' are the range of sheets at the bottom of my screen, B2:B50 is the range of cells in which I am trying to count the occurrences, and "TX" is my item I am trying to count.

The formula keeps showing me "0" as if it is counting incorrectly.

Any help on how to solve this?

Thanks!
Reply all
Reply to author
Forward
0 new messages