Struggling with sumproduct

**Angel160** · 01-03-2008, 06:31 AM

Hi there

I am struggling with sumproduct at the moment. This an extract of the spreadsheet I am working on:

Shares CP

1,600 WINS
40,631 WINS
Bulk WINS
Bulk WINS
35,354 WINS
31,826 WINS
Bulk WINS
7,101 ODL
15,438 WINS
6,000 WINS
Spouse WINS

I am trying to count up the number of deals done with a particular CP but not if they are "bulk or spouse" (e.g the numbe of deals done with WINS that aren't bulk or spouse = 6 and those done with ODL = 1).

Any help is gratefully received!

Thank you

**StephenR** · 01-03-2008, 06:39 AM

For WINS,

=SUMPRODUCT((B2:B12="WINS")*(A2:A12<>"Bulk")*(A2:A12<>"Spouse"))

However, if you're doing several of these pivot tables may be a better option.

**dominicb** · 01-03-2008, 06:40 AM

Good morning Angel160

This should work OK for you :
=SUMPRODUCT(--(B1:B11="WINS"),--(A1:A11<>"Bulk"),--(A1:A11<>"Spouse"))

If you struggle with the syntax for SumProduct, my add-in, available from the link below, has a SumProduct generator that will create the formula for you (just go to Ultimate > Formulae > Multiple SumIf Genreator) and fill in the criteria.

HTH

DominicB

**Angel160** · 01-03-2008, 06:50 AM

Thank you both so much - I was nearly there but couldn't quite nail it.

DomincB - what do the "--" in your formula do? I've not seen them before.

**dominicb** · 01-03-2008, 07:08 AM

Hi Angel160

The double minus sign is known as the unary minus. Forum poster corinereyes asked this question a few months back (http://www.excelforum.com/showthread.php?t=609883). This is what I wrote back :

Originally Posted by DominicB

SUMPRODUCT when used to do a comparison returns a boolean value (ie TRUE or FALSE).
To do what we require within the formula requires that TRUE / FALSE to be changed to a value.
The first negative does this and change TRUE to -1 and FALSE to 0.
Applying it again will keep the numeric value but change the - to a +. Thinking back to my schooldays, " a minus times a minus is a plus".
In cell A1 put an A, in B1 put 10, and then in another cell the formula
=SUMPRODUCT(-(A1="A"))
will return -1. Change the minus to a double minus and the result will be 1.
Change the formula to
=SUMPRODUCT(--(A1="A"),(B1))
will return 10 (ie, 1 x 10)
The formula will now return a 1 where a match is found and do what SUMPRODUCT was designed to do - multiply two numbers together and add it to a running total.

HTH

DominicB

Struggling with sumproduct

LinkBack

Thread Tools

Rate This Thread

Display

Struggling with sumproduct

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions