Sumproduct???

[kasbac]

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

 

[xld]

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

 

[Jonmo1]

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]

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.

 

[Evagrius Ponticus]

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

 

[Jonmo1]

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]

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]

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]

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]

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