ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Using COUNTIF in multiple wooksheets (https://www.excelbanter.com/excel-discussion-misc-queries/60967-using-countif-multiple-wooksheets.html)

Savage

Using COUNTIF in multiple wooksheets
 
I am trying to use countif to count the word "complete" in a range on
mulitple wooksheets. I select my range and then hold shift to select other
worksheets to include but it comes up with #value. When I do it with one
worksheet it works fine. Help would be appreciated.

Thanks

Kevin

Peo Sjoblom

Using COUNTIF in multiple wooksheets
 
COUNTIF does not work with multiple sheets

First put a list with all sheets in a range on the summary sheet, assume you
have 10 sheets you can put ALL their sheet names in for instance H1:H10,
assume you want to count in the range A1:A10 in those sheets

=SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H2&"'!A1:A10") ,"complete"))

--
Regards,

Peo Sjoblom

(No private emails please)


"Savage" wrote in message
...
I am trying to use countif to count the word "complete" in a range on
mulitple wooksheets. I select my range and then hold shift to select
other
worksheets to include but it comes up with #value. When I do it with one
worksheet it works fine. Help would be appreciated.

Thanks

Kevin



Savage

Using COUNTIF in multiple wooksheets
 
sorry I am not sure what you mean?? you may have to explain a little more
simple because I am not to familiar with these calculations.


"Peo Sjoblom" wrote:

COUNTIF does not work with multiple sheets

First put a list with all sheets in a range on the summary sheet, assume you
have 10 sheets you can put ALL their sheet names in for instance H1:H10,
assume you want to count in the range A1:A10 in those sheets

=SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H2&"'!A1:A10") ,"complete"))

--
Regards,

Peo Sjoblom

(No private emails please)


"Savage" wrote in message
...
I am trying to use countif to count the word "complete" in a range on
mulitple wooksheets. I select my range and then hold shift to select
other
worksheets to include but it comes up with #value. When I do it with one
worksheet it works fine. Help would be appreciated.

Thanks

Kevin




Peo Sjoblom

Using COUNTIF in multiple wooksheets
 
Don't know if I can explain further, what are your sheet names, for example
if your sheet names are
ASheet, BSheet, CSheet etc. you put each name in a cell t (in my example
H1:H10)

H1 ASheet
H2 BSheet
H3 CSheet
etc

meaning H1 holds A, H2 B, H3 C and so on, put the names of all sheets in a
list, change the A1:A10 in my formula to the range where you want to test
for "complete" in all sheets and replace H1:H10 with the range of the list
where you put the sheet names, then it is set to go
--
Regards,

Peo Sjoblom

(No private emails please)


"Savage" wrote in message
...
sorry I am not sure what you mean?? you may have to explain a little more
simple because I am not to familiar with these calculations.


"Peo Sjoblom" wrote:

COUNTIF does not work with multiple sheets

First put a list with all sheets in a range on the summary sheet, assume
you
have 10 sheets you can put ALL their sheet names in for instance H1:H10,
assume you want to count in the range A1:A10 in those sheets

=SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H2&"'!A1:A10") ,"complete"))

--
Regards,

Peo Sjoblom

(No private emails please)


"Savage" wrote in message
...
I am trying to use countif to count the word "complete" in a range on
mulitple wooksheets. I select my range and then hold shift to select
other
worksheets to include but it comes up with #value. When I do it with
one
worksheet it works fine. Help would be appreciated.

Thanks

Kevin






All times are GMT +1. The time now is 11:53 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com