SUMPRODUCT Help

G

Guest

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"))
 
G

Guest

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.
 
G

Guest

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

Scrabble Value calculation for Welsh words 0
Sumproduct Help! 2
Variable Sumproduct Range 6
Sumproduct help with a difference 3
Need help updating a table from a recordset. 1
Need code to update records in a table 1
Sumproduct formula problem 5
Excel Help with wildcard and sumproduct 1

Top