Use a Named Range with SumProduct

Not sure if this is possible..

I have the following formula that finds the value for "Available Hours" for "10TE01 LEO REILLY" in the "BW" segment of the attached spread sheet located in cell "B10"


Cell M2 contains the following formula

=INDEX('Month-1'!$A$1:$G$6000,MATCH(TRUE,INDEX(1/('Month-1'!$A$1:$A$6000=$L2)*ROW('Month-1'!$A$1:$A$6000)-MATCH($K$2,'Month-1'!$B$1:$B$6000,0)>0,),0),MATCH($M$1,'Month-1'!$A$14:$G$14,0))

This is just an example of several different values I need to find in this data sheet that can be 5000 or more lines.

This is tested and works great to find this value for any employee entered into cell K2. The formula is designed to account for the possibility of additional lines being added to the data sheet, something I can not control.

What I want to be able to do is substitute the value in K2 with a named range and return the sum of the values for "Available Hours" for that range or team of employees. If I can get that to work I think I can figure out how build a similar formula that would return the Average for other data points for a team.

I have gotten this far with a lot of help from the folks in this forum, so thank you for any further considerations!!!

Hi, try solution below:

Formula:

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

This seems to working great! Solid solution to the task at hand.. Thank you, Thank you, Thank you!! I will work on a similar formula to count the values , then combine the 2 formulas to find an average.

Thanks Again!!

You're welcome, thank you for the feedback and reputation
Glad it helps.



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Alvin, thank you again, I am struggling with modifying the formula you provided to return an average rather then a sum of a particular data point in the same work book. Some times I will need that result by team as well so I know we are close.

I figure I need to add the ability for your formula to first count the number of values I am summing, then divide the actual sum by that number.. correct?


Again, thanks for any suggestions!

Do you mean:

Formula:

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Once again, Perfect! You have saved me many hours. Thank you for your time and sharing your skills in helping me learn!

You're welcome, have fun with Excel!



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin