# Sumproduct???

#### kasbac

##### Active Member
Hi there

I have a little issue with a sumproduct that I was wondering if I could get some help with. Im not sure if the solution would be to go away from sumproduct?

I would like for the formula to sum colloum A if in the same line:

Colloum I = claimin
Colloum M = Q2 or Q3
N does not equal cancelled, withdrawn or rejected.

Hope that some one will be able to help me out

KB

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### xld

##### Banned
=SUMPRODUCT((I2:I20="Claimin")*(NOT(ISNUMBER(MATCH(N2:N20,{"Cancelled","Withdrawn","Rejected"},0))))*
(M2:M20={"Q2","Q3"})*A2:A20)

#### Jonmo1

##### MrExcel MVP
Try this

=SUMPRODUCT(--(I1:I100="Claimin"),--(M1:M100=Q2)+(M1:M100=Q3),--(NOT(ISNUMBER(MATCH(N1:N100,{"Cancelled","Withdrawn","Rejected"})))),A1:A100)

Hope This Helps.

#### Jonmo1

##### MrExcel MVP
Ahh, xld pointed out a difference in our perception of the stated problem...

If Q2 and Q3 are being used as the literal TEXT strings "Q2" "Q3", then use xld's solution.
If Q2 and Q3 are Cell References, use Mine.

Last edited:

#### Evagrius Ponticus

##### Well-known Member
Junmo - how do you make only part of a formula an array? Did you manually type in the brackets?

#### Jonmo1

##### MrExcel MVP
Junmo - how do you make only part of a formula an array? Did you manually type in the brackets?

Yes, those brackets are manually added. They are used to create an array of Text Strings. CSE is not required on this formula, because sumproduct is already an array formula..

#### Evagrius Ponticus

##### Well-known Member
Thanks Junmo1 - one more question. Why doesn't Xld coerce his sumproduct with the double negatives? I thought that since the "claimin" is a string, sumproduct will generate an array of "TRUE", "FALSE" and those have to be converted to numbers before they can be added? Thanks,

#### Jonmo1

##### MrExcel MVP
xld used the * to coerce the TRUE/FALSE to 1/0

The coerceing can be done in several ways

--(true/false)
(true/false)*1
(true/false)+0
(true/false)*(true/false)
n((true/false))

#### xld

##### Banned
Thanks Junmo1 - one more question. Why doesn't Xld coerce his sumproduct with the double negatives? I thought that since the "claimin" is a string, sumproduct will generate an array of "TRUE", "FALSE" and those have to be converted to numbers before they can be added? Thanks,

Because the way that I test for the strings Q2 and Q3, the OR condition, doesn't work if using the double unary.

It would work if I used

=SUMPRODUCT(--(I2:I20="Claimin"),--(NOT(ISNUMBER(MATCH(N2:N20,{"Cancelled","Withdrawn","Rejected"},0)))),
--((M2:M20="Q2")+(M2:M20="Q3")),A2:A20)

#### xld

##### Banned
Thanks Junmo1 - one more question. Why doesn't Xld coerce his sumproduct with the double negatives? I thought that since the "claimin" is a string, sumproduct will generate an array of "TRUE", "FALSE" and those have to be converted to numbers before they can be added? Thanks,

TRUE*TRUE =1
TRUE*FALSE=0
FALSE*TRUE=0
FALSE*FALSE=0

Replies
4
Views
573
Replies
14
Views
304
Replies
0
Views
526
Replies
5
Views
1K
Replies
5
Views
119

1,191,172
Messages
5,985,087
Members
439,940
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back