Sumproduct question

[Guest]

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

 

[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

 

[Guest]

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.

 

[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

 

[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

 

[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?

 

[Guest]

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

 

[Dave Peterson]

=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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Guest]

Thank you. I've wondered about that for a while.