SUMIFS help

[Polanskiman]

Hello,

I need some help regarding the SUMIFS function. Here is my formula:

=SUMIFS($N$31:$N$39,$L$31:$L$39,"yes",$M$31:$M$39,">0",$N31:$N39,"<=$K$16*1%",$N31:$N39,">=$M31:$M39")

As you can see the last 2 criteria cannot be computed by SUMIFS since it's expecting a value or a text.

I will explicit the last 2 conditions just in case they are not properly written:
- Condition 3: any cells in the $N31:$N39 range should be lower than $K$16*1%
- Condition 4: any cells in the $N31:$N39 range should be lower or equal to cells in $M31:$M39 range.

What do you suggest?

Thank you.

 

[jasonb75]

You just need to move the cell refs outside of the double quotes.

=SUM(SUMIFS($N$31:$N$39,$L$31:$L$39,"yes",$M$31:$M$39,">0",$N31:$N39,"<="&$K$16*1%,$N31:$N39,">="&$M31:$M39))

Although it might need some tweaks to allow for condition 4.

 

[jasonb75]

Too late to edit reply above. Based on condition 4 the last criteria should be <= not >=. Although I think that the entire formula might need to be rewritten as sumproduct.

=SUMPRODUCT($N$31:$N$39*($L$31:$L$39="yes")*($M$31:$M$39>0)*($N$31:$N$39<=($K$16*1%))*($N$31:$N$39>=$M$31:$M$39))

The sumifs method would compare any cell in N31:N39 to any cell in M31:M39 rather than comparing row by row which I believe would be what you actually want.

 

[Polanskiman]

You just need to move the cell refs outside of the double quotes.

=SUM(SUMIFS($N$31:$N$39,$L$31:$L$39,"yes",$M$31:$M$39,">0",$N31:$N39,"<="&$K$16*1%,$N31:$N39,">="&$M31:$M39))

Although it might need some tweaks to allow for condition 4.

That one didn't seem to be work. Was giving some funky results.

Too late to edit reply above. Based on condition 4 the last criteria should be <= not >=. Although I think that the entire formula might need to be rewritten as sumproduct.

=SUMPRODUCT($N$31:$N$39*($L$31:$L$39="yes")*($M$31:$M$39>0)*($N$31:$N$39<=($K$16*1%))*($N$31:$N$39>=$M$31:$M$39))

The sumifs method would compare any cell in N31:N39 to any cell in M31:M39 rather than comparing row by row which I believe would be what you actually want.

This one did the trick. Not used to using the SUMPRODUCT and yes criteria should have been <= and not >=. I corrected that and now I am getting what I need. Thank you very much.