SUMPRODUCT Help

M

Matt

Hi All,
I'm having trouble using the SUMPRODUCT formula. It is the first time I
have tried to use it. Below is some sample data.

Field 1 Field 2

Surrender 2
Disbursment 2
Surrender 1
Surrender 2

I am using the following formula:
=SUMPRODUCT((B2:B5)=2)*(A2:A5="Surrender")
I want the formula to return the value 2 as the combination of Surrender and
2 occurs 2 times. However it is returning the value of 0. Any help or
direction is greatly appreciated.
Thanks,

Mate.
 
P

Peo Sjoblom

=SUMPRODUCT(--(B2:B5=2),--(A2:A5="Surrender"))

or

SUMPRODUCT((B2:B5=2)*(A2:A5="Surrender"))
 
S

Stu Gnu

Peo

I hope you don't mind me using this thread to ask a related question, but
what does "--" do in the formula you supplied?
 
D

David Biddulph

The double unary minus combination coerces a TRUE or FALSE boolean variable
to a number 1 or 0.
 
S

Stu Gnu

Thanks David - I've not used this before.

David Biddulph said:
The double unary minus combination coerces a TRUE or FALSE boolean variable
to a number 1 or 0.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumproduct Help! 3
Sumproduct not working 5
Sumproduct formula problem 5
Count the remaining weekdays in a data range excluding holidays and start/end dates 5
Counting cells with SUMPRODUCT and conditionals...? 1
Variable Sumproduct Range 6
Count Number of Unique Cells That Don't Have Certain Characters 0
Sumproduct in matrix means 3

Top