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

 

[Aladin Akyurek]

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

=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)))

 

[Jonmo1]

There are definate advantages and disadvantages to all methods...

Where the -- will not work for the m1:m100={"Q2","Q3"], but the * method does...

However,

the * method will fail if there are text values in any of the cells in the Sum Range A1:A100, but the -- method will not

 

[Evagrius Ponticus]

Thanks fellas - I admit - it's simple but it doesn't make sense to me yet - but I'll figure it out by the end of the day

 

[Evagrius Ponticus]

I see, that's why you used --((M2:M20="Q2")+(M2:M20="Q3")),. I tried to use the OR function with sumproduct but kept getting a VALUE! error.

 

[Jonmo1]

Yep, that's the syntax I use for OR in Sumproduct. I've never been able to get Or to work either...

 

[kasbac]

hi guys

thx for the quick reply, i just forgot one further condition that i was hopeing for some help to incorporate:

the value in K need to = the cell value in coloum R

so what i need is these four conditions:

Colloum I = claimin (text string)
Colloum M = Q2 or Q3 (text string)
N does not equal cancelled, withdrawn or rejected (text strings)
value in colloum K needs to = the cell value in colloum R (number)

once again thx for all the help

 

[Jonmo1]

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

 

[Aladin Akyurek]

hi guys

thx for the quick reply, i just forgot one further condition that i was hopeing for some help to incorporate:

the value in K need to = the cell value in coloum R

so what i need is these four conditions:

Colloum I = claimin (text string)
Colloum M = Q2 or Q3 (text string)
N does not equal cancelled, withdrawn or rejected (text strings)
value in colloum K needs to = the cell value in colloum R (number)

once again thx for all the help

Do you mean...

=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))
  --($K$2:$K$100=$R$2:$R$100))

or is it a specific value in R like in...

=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))
  --($K$2:$K$100=$R$2))

 

[kasbac]

Hi there

Hmmm i seem to be having som problems applying the formula? When I try to use xld first formula:

I get an error and the area "N20," is highlighted?


by the way the data that i need summed in colloum A is numbers

 

[Aladin Akyurek]

Hi there

Hmmm i seem to be having som problems applying the formula? When I try to use xld first formula:

I get an error and the area "N20," is highlighted?


by the way the data that i need summed in colloum A is numbers

Did you try alternative set ups?