| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple Counting Criteria | Excel Discussion (Misc queries) | |||
| Help counting multiple Criteria | Excel Discussion (Misc queries) | |||
| Counting by multiple criteria | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Linear Mode
