Looking for a function

[Guest]

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

 

[T. Valko]

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

 

[T. Valko]

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

 

[Roger Govier]

Hi

Assuming the data is in columns A and B, enter the date desired in C1
and in D1 enter
=SUMPRODUCT(($A$1:$A$1000=$C$1)*($B$1:$B$1000=1))

Change ranges to suit, but ensure that they are of equal size.

 

[Guest]

I tried to do so but i got #NAME? error for the next data:

A B
01/01/2007 1
01/01/2007 0
01/01/2007 1
04/01/2007 0
05/01/2007 0
06/01/2007 1
07/01/2007 1
08/01/2007 1
09/01/2007 0
10/01/2007 0

(I used : =SUMPRODUCT(--(Dates=DATE(2007,1,1)),--(Values=1)))

Eli

 

[Guest]

Sorry, I forgot to define the names of the ranges.
Now it works fine.

Thanks!!!

 

[Guest]

Roger - Thanks!
You saved my next question

Hi

Assuming the data is in columns A and B, enter the date desired in C1
and in D1 enter
=SUMPRODUCT(($A$1:$A$1000=$C$1)*($B$1:$B$1000=1))

Change ranges to suit, but ensure that they are of equal size.