SumProduct Question

[HYKE]

HI,

I cant seem to make this formula work. It is giving me zero instead of sum

=SUMPRODUCT((AD7:AD200="Mar")*(C7:C200="RO 9414"),P7:P200)

Where AD7:AD200 lies the date formatted as "mmm"
C7:C200 lies the product codes (which I have many)
P7:P200 where the qty to sum is located.

What seems to be missing here?

thanks!!!

 

[VoG]

Try

=SUMPRODUCT(--(TEXT(AD7:AD200,"mmm")="Mar"),--(C7:C200="RO 9414"),P7:P200)

 

[HYKE]

Hi Vog,

Initially it worked, however when I changed the other search criteria, again it is giving me zero.
=SUMPRODUCT(--(TEXT($AD$6:$AD$200,"mmm")="Mar"),--($C$6:$C$200="150 SN"),$P$6:$P$200)
=SUMPRODUCT(--(TEXT($AD$6:$AD$200,"mmm")="Mar"),--($C$6:$C$200="500 SN"),$P$6:$P$200)
=SUMPRODUCT(--(TEXT($AD$6:$AD$200,"mmm")="Mar"),--($C$6:$C$200="BATTERY"),$P$6:$P$200)

I tried referring this criteria to a cell but the same result I get-zero.
=SUMPRODUCT(--(TEXT($AD$6:$AD$200,"mmm")="Mar"),--($C$6:$C$200=C6),$P$6:$P$200)


Thanks,
HYKE

 

[Aladin Akyurek]

What does...

=SUMPRODUCT(--(TEXT($AD$6:$AD$200,"mmm")="Mar"),--($C$6:$C$200="150 SN"),$P$6:$P$200+0)

give?

By the way, not testing for month/year is a risky approach...

 

[texasalynn]

try changing your ranges to just a single line that you know would create a count of 1. Then open the formula dialog box and see what is there? Does it show 1's for each line?

Also the text you are testing for does the data match exactly? No extra spaces?