|
Counting using multiple criteria
I need an array formula that will do the following. Count (not sum) the
number of cells in column C that are above the value of 0 if the adjacent cells in Column A are equal to a value of 1. -- Thanks Shawn |
Counting using multiple criteria
=sumproduct(--(A1:A65536=1),--(C1:C655360))
"Shawn" wrote: I need an array formula that will do the following. Count (not sum) the number of cells in column C that are above the value of 0 if the adjacent cells in Column A are equal to a value of 1. -- Thanks Shawn |
Counting using multiple criteria
What is the significance of the "--"s? Also, what if I want to change the
formula as follows? =sumproduct(--(A1:A65536=B1),--(C1:C655360)) as opposed to =sumproduct(--(A1:A65536=1),--(C1:C655360)) -- Thanks Shawn "TomPl" wrote: =sumproduct(--(A1:A65536=1),--(C1:C655360)) "Shawn" wrote: I need an array formula that will do the following. Count (not sum) the number of cells in column C that are above the value of 0 if the adjacent cells in Column A are equal to a value of 1. -- Thanks Shawn |
Counting using multiple criteria
First, you can't use the entire column in xl2003 and older. So adjust your
ranges accordingly. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Shawn wrote: What is the significance of the "--"s? Also, what if I want to change the formula as follows? =sumproduct(--(A1:A65536=B1),--(C1:C655360)) as opposed to =sumproduct(--(A1:A65536=1),--(C1:C655360)) -- Thanks Shawn "TomPl" wrote: =sumproduct(--(A1:A65536=1),--(C1:C655360)) "Shawn" wrote: I need an array formula that will do the following. Count (not sum) the number of cells in column C that are above the value of 0 if the adjacent cells in Column A are equal to a value of 1. -- Thanks Shawn -- Dave Peterson |
| All times are GMT +1. The time now is 01:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com