Looking for a function
ooops!
If you need to count for the date and the number 0:
=SUMPRODUCT(--(Dates=DATE(2007,1,1)),--(ISNUMBER(Values)),--(Values=1))
The correct formula should be:
=SUMPRODUCT(--(Dates=DATE(2007,1,1)),--(ISNUMBER(Values)),--(Values=0))
SUMPRODUCT will evaluate an empty cell as 0 so we need to add a test that
makes sure there is actually a number in the Values cells.
Biff
"T. Valko" wrote in message
...
Try this for a specific date and the number 1:
A1:A10 = Dates
B1:10 = values
=SUMPRODUCT(--(Dates=DATE(2007,1,1)),--(Values=1))
Change the date as needed.
If you need to count for the date and the number 0:
=SUMPRODUCT(--(Dates=DATE(2007,1,1)),--(ISNUMBER(Values)),--(Values=1))
Better to use cells to hold the criteria:
D1 = some date like 1/1/2007
E1 = 1
=SUMPRODUCT(--(Dates=D1),--(ISNUMBER(Values)),--(Values=E1))
Biff
"???" wrote in message
...
Hi
I am looking if there is a function that can retrive the number of cells
with specific value in one column according to a specific value in
another
column. for example:
A table with 2 columns, one for dates and the other is 0 or 1 values. I
am
wondering if there is a function that can tell me the number of cells
with
the value 1 in specific date.
Thanks in advance
Eli
|