johnnyloff
asked on
SUMPRODUCT function
I am trying to use the SUMPRODUCT function but it's new to me. The key formula is in cell E22. I am trying to sum the values in the range C6:R9 for all values that are greater than the year 2010 and less than the variable year in B21.
As it is, it will only sum the values in one row for the range C6:R6. How can I fix this?
Exp-Analysis.xls
As it is, it will only sum the values in one row for the range C6:R6. How can I fix this?
Exp-Analysis.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
=SUMPRODUCT(C6:R6*(C4:R4>= 2010)*(C4: R4<=B21))
ASKER
Perfect.
you don't really need the 2010<B21 part, so you can do with
=SUMPRODUCT(C6:R6,((C4:R4> =2010)*(C4 :R4<=B21)) )
This will only return row 6, if you want more row, you need to add more sumproducts.
=SUMPRODUCT(C6:R6,((C4:R4> =2010)*(C4 :R4<=B21)) )+SUMPRODU CT(C7:R7,( (C4:R4>=20 10)*(C4:R4 <=B21)))
etc
Or am I missing part of your question,
THomas
=SUMPRODUCT(C6:R6,((C4:R4>
This will only return row 6, if you want more row, you need to add more sumproducts.
=SUMPRODUCT(C6:R6,((C4:R4>
etc
Or am I missing part of your question,
THomas
=SUMPRODUCT(C6:R9*(C4:R4>=
barry