Solved: Sumproduct - Summing

[Hoopsah]

Hi

trying to put a SUMPRODUCT formula in my workbook and I am now tying myself in knots.

Column A Column B Column C
Feb Ness 7
Feb Don 3
Feb Tay 1
Feb Clyde 2
Feb Ness 1

I want to be able to add Column C for all that have the properties Feb, Ness, Don + Tay

SUMPRODUCT(($A6:$A$5404=G7),($C$6:$C$5404="Ness")+($C$6:$C$5404="Don")+($C$6:$C$5404="Tay")),$E$6:$E$5404))

I finally got to this but it's wrong.

Any help appreciated

[Bob Phillips]

Try

=SUMPRODUCT(($A6:$A$5404=G7)*($C$6:$C$5404={"Ness","Don","Tay"})*($E$6:$E$5404))

watch out for the injected spaces.

[Hoopsah]

Perfect!!!

Once again you have lived up to your title of Distinguished Lord of VBAX :)

Thanks Bob

Gerry

[Hoopsah]

Hi Bob

hope I didn't jump the gun earlier. When I try this it seems to adding the Don & Tay figures but missing the Ness figures??

Am I missing something?

[mdmackillop]

Hi Gerry,
Can you post your test data?

[Hoopsah]

Sure, please find copy attached.

Going forward I would probably like to add another condition into the formula that only counts if it is a Non Std Connection or and so on.

All these results will have to go into a pivot table and chart....but I am getting ahead of myself :confused2

[mdmackillop]

Try replacing "Ness " with "Ness". There's a monster of a gap in there!

[Hoopsah]

:doh:

Don't believe that!!!!!

Thanks for pointing that out to me - works perfectly - thread still solved!

Thanks again

[Bob Phillips]

Gerry,

Use

=SUMPRODUCT(($A$4:$A$5404=H7)*(TRIM($B$4:$B$5404={"Ness","Don","Clyde"}))*($D$4:$D$5404))

and then just append another condition

=SUMPRODUCT(($A$4:$A$5404=H7)*(TRIM($B$4:$B$5404)={"Ness","Don","Clyde"})*($C$4:$C$5404="Non Std Connection")*($D$4:$D$5404))

[Hoopsah]

Thanks Bob - I have put the amendment in and tried putting some spaces back in to test and it works great

Cheers again for all your help