Counting a Column when Three Conditions are true

[Guest]

I am having a difficult time finding anyone who can answer this question for
me.

A B C D
1 7/17/2006 06.123.456 224
2 7/17/2006 06.123.457
3 7/17/2006 06.123.458
4 7/17/2006 06.123.459 34
5 7/17/2006 06.123.460
6 7/17/2006 06.123.461
7 7/17/2006 06.123.462 32
8 7/17/2006 06.123.463 32
9 7/18/2006 06.123.464
10 7/19/2006 06.123.465
11 7/20/2006 06.123.466 32
12 7/21/2006 06.123.467
13 7/22/2006 06.123.468

Basically I need a formula to do the following: When Column "A" (Date)
equals a certain date (i.e.: 7/17/06), then look at corresponding columns in
that row where Column "C" (.06.123.456) and Column "D" (224). If they both
have a value (TRUE), then it has a value of (1), then sum all the instances
where it is true. In the example above, for 7/17/06 the answer would be 4.
The closestr thing I got wa the following function:

=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A205)*('Service Request
Log'!$C$4:$C$5000<>"0"))+SUMPRODUCT(--('Service Request
Log'!$B$4:$B$5000=$A205)*('Service Request Log'!$O$4:$O$5000)<>0)

But it is summing the two columns ( "C" & "D" in the example) together so I
get a value of "12".

Thanks for any help someone might be able to give me.

 

[Die_Another_Day]

Try this array formula:
=SUM(IF(A1:A10=F1,IF(C1:C10<>"",IF(D110<>"",1,0),0),0))
then press Ctrl+Shift+Enter
Change the ranges as needed, F1 holds the search value

HTH

Die_Another_Day

 

[Guest]

Hi Jm,

I am sorry I did not understand your sample, on this case, there is just one
7/17/2006 - 06.123.456 - <>0 and the formula should return 1 not 4.

I have used =sumproduct(--(c5:c17="7/17/2006")*(e5:e17=6123456)*(f5:f17<>0))
and its run

please if it do not solve your problem let me know

hth
regards from Brazil
Marcelo

"jimswinder" escreveu:

 

[Guest]

HTH...you are a life saver!!!! IT worked in m y small example...now I will
try in my actuall spreadsheet.

Thanks!!!!

 

[Guest]

Try:

=SUMPRODUCT(--(B1:B13=G1),--(C1:C13<>""),--(D113<>""))

g1=date (17/7/2006)

 

[Guest]

Sorry Marcelo...you misunderstood what I needed. The only thing it had to
equal was the date...the other two columns just had to be a value greater
than zero.

 

[Guest]

Toppers:

YOur formula works also in my example..I will try and plug it into my real
spreadsheet and see if it also works there.

 

[Guest]

Thanks again to everyone that helped with this formula...all the formulas
that were sugggested work in my actual spreadsheet.

You guys who answer our pleas for help do a great job!!!!!!

Thanks again!!!