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

"janee" <ja...@discussions.microsoft.com> wrote in message
news:93B81271-4F3C-42B1...@microsoft.com...

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

--
Biff
Microsoft Excel MVP

"janee" <ja...@discussions.microsoft.com> wrote in message
news:93B81271-4F3C-42B1...@microsoft.com...

[T. Valko]

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]

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]

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]

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]

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]

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.

--
Biff
Microsoft Excel MVP

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

[Draven Shean]

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!

[Wei Zhen]

Hello!

I have read the above messages and done many research. I am trying to count the number of occurences through many sheet tab in one google sheet.

I.e.:
Sheetlist with all the Sheet Tab names: S2:S
Data range: U4:U
Criteria: B2

My formula should be =SUMPRODUCT(COUNTIF(INDIRECT("'"&S2:S&"'!U4:U"),B2))

However despite testing, it only shows the results of in sheet tab named in S2. Please help

--
This email including its attachments is confidential and may contain
information that is privileged. Access to this email by anyone other than
the intended recipient is unauthorised. If you are not the intended
recipient (or if you are responsible for the delivery of the same to the
intended recipient) or if you received this email in error, any disclosure,
copying, distribution or use of this email (or any part of its contents) is
prohibited. If you have received this email in error, please notify us
immediately by email or telephone and delete this email from your system.
Unless otherwise indicated by an authorised representative, the views,
opinions, conclusions and/or other information expressed in this email are
not given or endorsed by Elitez Pte Ltd, its subsidiaries and associates
companies. Please be aware that messages sent to and from Elitez Pte Ltd,
its subsidiaries and associates companies may be monitored for reasons of
security, to protect our business, and to ensure compliance with legal and
regulatory obligations and our internal policies. Emails are not a secure
method of communication, can be intercepted and cannot be guaranteed to be
free from errors. Anyone who communicates with us by email is taken to
understand and accept the above.