PDA

View Full Version : Solved: Sumproduct - Summing



Hoopsah
06-02-2010, 07:07 AM
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
06-02-2010, 07:37 AM
Try

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

watch out for the injected spaces.

Hoopsah
06-02-2010, 11:47 PM
Perfect!!!

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

Thanks Bob

Gerry

Hoopsah
06-03-2010, 04:09 AM
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
06-03-2010, 04:32 AM
Hi Gerry,
Can you post your test data?

Hoopsah
06-03-2010, 04:47 AM
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
06-03-2010, 05:08 AM
Try replacing "Ness " with "Ness". There's a monster of a gap in there!

Hoopsah
06-03-2010, 05:12 AM
:doh:

Don't believe that!!!!!

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

Thanks again

Bob Phillips
06-03-2010, 05:16 AM
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
06-10-2010, 05:26 AM
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