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
johnnyloffAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
Hello johnnyloff,

try this with small syntax change

=SUMPRODUCT(C6:R9*(2010<B21)*(C4:R4>=2010)*(C4:R4<=B21))

regards, barry
0
 
barry houdiniCommented:
....I don't think you need (2010<B21) either, so you can use just

=SUMPRODUCT(C6:R9*(C4:R4>=2010)*(C4:R4<=B21))

barry
0
 
santoshmotwaniCommented:
=SUMPRODUCT(C6:R6*(C4:R4>=2010)*(C4:R4<=B21))
0
 
johnnyloffAuthor Commented:
Perfect.
0
 
nutschCommented:
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)))+SUMPRODUCT(C7:R7,((C4:R4>=2010)*(C4:R4<=B21)))

etc

Or am I missing part of your question,

THomas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.