I have a working formula where column W is the range, and the criteria is LESS THAN OR EQUAL TO -0.15.
=SUMPRODUCT((W:W<=-0.15)*(SUBTOTAL(103,OFFSET(INDEX(W:W,1),ROW(W:W)-MIN(ROW(W:W)),0)))) However, I'm trying to add a criteria to the above formula to include GREATER THAN -0.25
I'm getting stumped on how to do it.
This is what I tried with no luck:
=SUMPRODUCT((W:W<=-0.15,W:W>-0.25)*(SUBTOTAL(103,OFFSET(INDEX(W:W,1),ROW(W:W)-MIN(ROW(W:W)),0)))) You have to use the * operator to combine multiple conditions within the SUMPRODUCT function, not a comma:
=SUMPRODUCT( (W:W<=-0.15)* (W:W>-0.25)* (SUBTOTAL(103,OFFSET(INDEX(W:W,1),ROW(W:W)-MIN(ROW(W:W)),0)))) Edit, if you have Excel 365, you might consider using the LET function to simplify debugging and setting the required range:
=LET( range, W:W, condition1, (range <= -0.15), condition2, (range > -0.25), visibleCells, SUBTOTAL(103, OFFSET(INDEX(range, 1), ROW(range) - MIN(ROW(range)), 0)), SUMPRODUCT(condition1 * condition2 * visibleCells) )
AGGREGATE()function.