SUMIFS with different range sizes

[exeliscious]

Hi everyone,

I am trying to roll positive monthly cash flows from one table into 'Total Revenue' by year in another sheet.

Please Login or Register  to view this content.

seems to work to provide all cash flows for each year. However, if I try to move to a SUMIFS statement to add the condition of positive numbers only :

Please Login or Register  to view this content.

I get the #VALUE error as the ranges are not the same size. Hoping someone can help me on this and apologies if my code in the below worksheet is unruly but I'm a total Excel novice.

Thanks in advance!

[Pete_UK]

Try it with SUMPRODUCT, like this in E5:

=SUMPRODUCT(Model!F18:L318,(Model!P18:P318=E3)*(Model!F18:L318>0))

You may need to anchor the ranges if you wish to copy across and/or down.

Hope this helps.

Pete

[hrlngrv]

While the 1st formula you show may return a numeric result, I don't believe it returns a correct result. I believe Excel only sums the values in Model!F18:F318. Indeed, if I have {1,20;300,4000} in C3:D4, {1;0} in A3:A4, =SUMIF(A3:A4,1,C3:D4) returns 1 rather than 21 in web Excel.

In any case, you need to use SUMPRODUCT.

Please Login or Register  to view this content.

[davsth]

You haven't provided your expected answer is the below any closer
=SUMPRODUCT((Model!F18:L318)*(Model!P18:P318=E3)*(Model!F18:L318>0))

[exeliscious]

Thank you all! That helped me find another mistake as well.