sumproduct() with sumifs()?

[hatzopoulos]

HELP!! I want to sum calculated values across columns, but only for rows meeting certain criteria.

For example,

Sumifs(a:a*b:b/c:c, D:D, >5, D:D, <10, E:E="yes") where the result is the sum of each row [a*b/c] where d is greater than 5 AND less than 10 AND E = "yes")

A ----B----C----D----E
12 7 2 4 Yes
11 8 4 6 NO
8 6 6 3 NO
7 12 4 12 YES
14 4 8 7 YES
10 5 25 8 YES

result=7+2 = 9

Thanks for any help or direction you can provide!

[Nisha Dhawan]

Hi,

Please refer below formula, as I understand your requirement is Cell D1 should be greater than 5 and less than 10 and E1 Cell is "yes" then formula should give the result A1*B1/C1.

=IF(AND(D1>5,D1<10,E1="yes"),A1*B1/C1,B1+C1)

Please check if its works, otherwise please attached the Excel file.

[adhawan06]

=IF(AND(D1>5,D1<10,E1="yes"),A1*B1/C1,B1+C1)

Small Correction in your formula - Try this in F2 and drag it to F6 then you can sum up in F8. - =IF(AND(E2="Yes",D2>5,D2<10),SUM(A2*B2/C2),"")

[hatzopoulos]

I was hoping to do this without needing another column to generate values for each row and then summing them.. Looking for something similar to a single Sumifs() and or sumproduct() function. Also all the values calculated and the columns with the criteria are from another workbook

[FDibbins]

Try this...
=SUMPRODUCT(((E2:E7="Yes")*(D2:D7>5)*(D2:D7<10)*(A2:A7)*(B2:B7)/(C2:C7)))