Sumproduct question

B

Barb Reinhardt

What am I doing wrong here:

=SUMPRODUCT(AL3:AL84>="3/1/2006")*(AP3:AP84=6)

I want to count the number of times AL3:AL84 >= 3/1/2006 AND AP3:AP84 = 6.
I keep getting ZERO when this is a non zero number. I have entered it with
CTRL SHIFT ENTER (I think that's it) and it has {} around it.

Thanks,
Barb Reinhardt
 
B

Bernard Liengme

Try =SUMPRODUCT(--(AL3:AL84>=date(2006,3,1)),--(AP3:AP84=6))
I am assuming you use US date convention
Confirm with simple ENTER
 
B

bpeltzer

I'd try =sumproduct(--(al3:al84>=date(2006,3,1),--(ap3:ap84=6)) with no array
required. "3/1/2006" is forcing a text comparison; date(2006,3,1) converts
to Excel's numeric representation of the date 3/1/06.
 
S

SteveG

Barb,

Try this,

=SUMPRODUCT((AL3:AL84>=DATE(2006,3,1))*(AP3:AP84=6))

You were missing some parenthesis and when you put "3/1/2006" in, th
formula is looking for a text string equal to that but excel sees date
as numbers not text. You could also type the date in another cell an
refer to it in the formula rather than using the DATE formula.

HTH

Stev
 
P

Peo Sjoblom

You need more parenthesis and you need to convert the text "3/1/2006"

=SUMPRODUCT((AL3:AL84>=--"3/1/2006")*(AP3:AP84=6))

although this is better

=SUMPRODUCT((AL3:AL84>=--"2006-03-01")*(AP3:AP84=6))

since it will work with different regional settings

no need to array enter it


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
D

Dave Peterson

I'd use:

=sumproduct(--(al3:al84>=date(2006,3,1)),--(ap3:ap84=6))

And ap3:ap84 contains the number 6, not the string '6 (as text), right?
 
B

Barb Reinhardt

Thanks everyone. Can someone explain what the -- in the sumproduct formula
means?
 
Top