Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Occurances across a range of sheets
I have a number of identical sheets, and I'd like to be able to count
the occurances of a certain entry in a cell. When I use the formula: =COUNTIF('Sheet1:Sheet16'!H2, "W") the cell displays ##### (error in value). What's a formula I can use? Also, I'd like to be able to do a similar thing, with non-blank values (i.e. I'd like to count the number of non-blank cells across a number of identical sheets). Thanks in advance for your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Occurances across a range of sheets
Try these:
=SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT( "1:16"))&"!H2"),"W")) =SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT( "1:16"))&"!H2"),"<")) Biff wrote in message oups.com... I have a number of identical sheets, and I'd like to be able to count the occurances of a certain entry in a cell. When I use the formula: =COUNTIF('Sheet1:Sheet16'!H2, "W") the cell displays ##### (error in value). What's a formula I can use? Also, I'd like to be able to do a similar thing, with non-blank values (i.e. I'd like to count the number of non-blank cells across a number of identical sheets). Thanks in advance for your help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Occurances across a range of sheets
Hmmm, I tried those formulae, but I get an invalid cell reference
error after substituting in the respective sheet names. If I use =SUMPRODUCT(COUNTIF(INDIRECT('Sheet 1'!H2&ROW(INDIRECT("1:16"))&"! H2"),"W")) the cell displays the correct number of W's, but obviously I want to count across a number of sheets. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Occurances across a range of sheets
If your sheets are named Sheet1 through Sheet16, as per your original
post, there's no need to change Biff's formula. In article .com, wrote: Hmmm, I tried those formulae, but I get an invalid cell reference error after substituting in the respective sheet names. If I use =SUMPRODUCT(COUNTIF(INDIRECT('Sheet 1'!H2&ROW(INDIRECT("1:16"))&"! H2"),"W")) the cell displays the correct number of W's, but obviously I want to count across a number of sheets. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Occurances across a range of sheets
You don't need to change the sheet definition.
The sheet names are "built" within the formula by the first Indirect function. It creates an array of references like this: COUNTIF(Sheet1!,H2) COUNTIF(Sheet2!,H2) COUNTIF(Sheet3!,H2) COUNTIF(Sheet4!,H2) ... COUNTIF(Sheet16!,H2) Then, Sumproduct adds up all the results. If your sheet names are not the default names like, Sheet1, Sheet2, Sheet3, etc., then we can tweak the formula. Post back if that's the case. Biff "T. Valko" wrote in message ... Try these: =SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT( "1:16"))&"!H2"),"W")) =SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT( "1:16"))&"!H2"),"<")) Biff wrote in message oups.com... I have a number of identical sheets, and I'd like to be able to count the occurances of a certain entry in a cell. When I use the formula: =COUNTIF('Sheet1:Sheet16'!H2, "W") the cell displays ##### (error in value). What's a formula I can use? Also, I'd like to be able to do a similar thing, with non-blank values (i.e. I'd like to count the number of non-blank cells across a number of identical sheets). Thanks in advance for your help. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Occurances across a range of sheets
The sheet names are "built" within the formula by the first Indirect
function. It creates an array of references like this: COUNTIF(Sheet1!,H2) COUNTIF(Sheet2!,H2) COUNTIF(Sheet3!,H2) COUNTIF(Sheet4!,H2) .. COUNTIF(Sheet16!,H2) Actually, that array of references would look like this: COUNTIF(Sheet1!H2,"W") COUNTIF(Sheet2!H2,"W") COUNTIF(Sheet3!H2,"W") etc etc Biff "T. Valko" wrote in message ... You don't need to change the sheet definition. The sheet names are "built" within the formula by the first Indirect function. It creates an array of references like this: COUNTIF(Sheet1!,H2) COUNTIF(Sheet2!,H2) COUNTIF(Sheet3!,H2) COUNTIF(Sheet4!,H2) .. COUNTIF(Sheet16!,H2) Then, Sumproduct adds up all the results. If your sheet names are not the default names like, Sheet1, Sheet2, Sheet3, etc., then we can tweak the formula. Post back if that's the case. Biff "T. Valko" wrote in message ... Try these: =SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT( "1:16"))&"!H2"),"W")) =SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT( "1:16"))&"!H2"),"<")) Biff wrote in message oups.com... I have a number of identical sheets, and I'd like to be able to count the occurances of a certain entry in a cell. When I use the formula: =COUNTIF('Sheet1:Sheet16'!H2, "W") the cell displays ##### (error in value). What's a formula I can use? Also, I'd like to be able to do a similar thing, with non-blank values (i.e. I'd like to count the number of non-blank cells across a number of identical sheets). Thanks in advance for your help. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Occurances across a range of sheets
That's awesome guys, it works perfectly now.
Thanks for all your help! GB. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Occurances across a range of sheets
You're welcome. Thanks for the feedback!
Biff wrote in message ps.com... That's awesome guys, it works perfectly now. Thanks for all your help! GB. |
#9
|
|||
|
|||
What if someone wanted to add the values that match the criteria found? I.E. for every time X was found in column A and Y was beside it in Column B, then add the value (Also adjacent to them in the same row) in Column D. Is this possible? And not counting the occurences, just the value in Column D all the way down the sheet.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting each time text appears in range??? | Excel Worksheet Functions | |||
Common range name for 2 sheets | Excel Discussion (Misc queries) | |||
Match function...random search? | Excel Worksheet Functions | |||
count date occurances in range of dates... | Excel Worksheet Functions | |||
Counting within a filtered range | Excel Worksheet Functions |