COUNTIF across worksheets

[pdberger]

Good morning --

I'm shooting for a COUNTIF formula that will count text occurances across
multiple worksheets. I can get the formula to work if the data is on the
same worksheet but as soon as I go to multiple ones, it fails and I get a
'#VALUE!' error.

Here's the formula:

=COUNTIF(First:Last!A1,"Y")

Thanks in advance.

 

[Ron Rosenfeld]

Good morning --

I'm shooting for a COUNTIF formula that will count text occurances across
multiple worksheets. I can get the formula to work if the data is on the
same worksheet but as soon as I go to multiple ones, it fails and I get a
'#VALUE!' error.

Here's the formula:

=COUNTIF(First:Last!A1,"Y")

Thanks in advance.

COUNTIF does not work with 3D references.

You may be able to use something like:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A4:A7&"'!A1"),"Y"))

where A1:A4 contains the names of your worksheets.
--ron

 

[Lars-Åke Aspelin]

Good morning --

I'm shooting for a COUNTIF formula that will count text occurances across
multiple worksheets. I can get the formula to work if the data is on the
same worksheet but as soon as I go to multiple ones, it fails and I get a
'#VALUE!' error.

Here's the formula:

=COUNTIF(First:Last!A1,"Y")

Thanks in advance.

Have a look here:

https://www.microsoft.com/communiti...9bf-8fef-e528337714eb&lang=en&cr=US&sloc=&p=1

Hope this helps / Lars-Åke

 

[Mike H]

Hi,

You can't create a 3D formula with Countif, it doesn't support it so here's
another apptoach

Create a list of your sheet names in a column ( I used B1:B4) like this

First
Sheet3
Sheet4
last

Then the formula

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B1:B4&"'!A1"),"Y"))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.