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

 

[kasbac]

Hi aladin

If you by others setups meen if i have tried any of the other soulution then yes, for some reason i cant seem to get them to work. I have tried to put in the following code of yours, dont know if i have modified it to much?

=SUMPRODUCT(($A$2:$A$100);--($I$2:$I$100="claimin");--ISNUMBER(MATCH($M$2:$M$100,{"Q2","Q3"},0));--ISNA(MATCH($N$2:$N$100,{"cancelled","withdrawn","rejected"},0))

the section $M$100, is highlighted when i try to use the code

In this one I have NOT included my fourth condition

thx for any help

 

[Guanjin Peter]

not really a reply...just to show off: my 6 condition formula:

=SUMPRODUCT(--(db!$A$2:$A16=$B$1),--(db!$F$2:$F16=C$5),--(db!$G$2:$G16=C$6),--(db!$E$2:$E16=$A7),--(db!$D$2:$D16=$B7),--(db!$I$2:$I16=C8),db!$H$2:$H16)

 

[kasbac]

Hi peter thx for you input and your formula is kind of what im looking for but I also need condition that would disregard any cells that has the text string cancelled, withdraw or rejected

 

[Guanjin Peter]

but I also need condition that would disregard any cells that has the text string cancelled, withdraw or rejected

<!-- / message -->

well...if that happens what do you want it to do? return 0? return blank?

 

[Aladin Akyurek]

Hi aladin

If you by others setups meen if i have tried any of the other soulution then yes, for some reason i cant seem to get them to work. I have tried to put in the following code of yours, dont know if i have modified it to much?

=SUMPRODUCT(($A$2:$A$100);--($I$2:$I$100="claimin");--ISNUMBER(MATCH($M$2:$M$100,{"Q2","Q3"},0));--ISNA(MATCH($N$2:$N$100,{"cancelled","withdrawn","rejected"},0))

the section $M$100, is highlighted when i try to use the code

In this one I have NOT included my fourth condition

thx for any help

You need a closing paren at the end and replace some commas with semi-colons...

=SUMPRODUCT(($A$2:$A$100);--($I$2:$I$100="claimin");--ISNUMBER(MATCH($M$2:$M$100;{"Q2","Q3"};0));--ISNA(MATCH($N$2:$N$100;{"cancelled","withdrawn","rejected"};0)))

Would you also try...

=SUMPRODUCT(($A$2:$A$100);--($I$2:$I$100="claimin");--ISNUMBER(MATCH($M$2:$M$100;$X$2:$X$3;0));--ISNA(MATCH($N$2:$N$100;$Y$2:$Y$4;0)))

where X2:X3 houses Q2 & Q3, and Y2:Y4 the criterion items of cancelled, withdrawn, & rejected?

 

[kasbac]

the value in cell A should be disregarded

 

[Aladin Akyurek]

the value in cell A should be disregarded

One thing at a time. See my last post. If column A is irrelevant, remove the ($A$2:$A$100) bit...

 

[kasbac]

Sorry aldin my reply regarding colloum A was to Peter.

I have just tried your second code and that does the trick perfectly. Now is it posibble to "write in" the values Q2, Q3, Withdrawn, rejected and cancelled in the formula so i dont need the values in the cells?

 

[Aladin Akyurek]

Sorry aldin my reply regarding colloum A was to Peter.

I have just tried your second code and that does the trick perfectly. Now is it posibble to "write in" the values Q2, Q3, Withdrawn, rejected and cancelled in the formula so i dont need the values in the cells?

Select X2:X3 in the formula bar, hit F9, copy what you see, and replace X2:X3 with the bit you just copied. Do the same with the Y2:Y4 bit.

 

[kasbac]

Good job aladin!

That solved the issue thank you very much for all of your help.

And also to the rest of you guys