Vlookup with dynamic summing????

[Mark McInerney]

Hi All,

I have data in B1:BC102

In Column B there are department tags. From Column C thru BC there are department sales for each week number.

I want to be able to select a department from a drop down and also select two week numbers and then to return the sum of the sales for that department between those two weeks.

e.g. Grocery Sales Total from Week 2 thru to and including Week 7.

Any help appreciated.

I have been using the following in another worksheet, but as you can see the reference to the department that I am summing is fixed and not dynamic.

=SUM(INDEX('2018 Data'!D7:BC7,MATCH(K154,'2018 Data'!D5:BC5)):INDEX('2018 Data'!D7:BC7,MATCH(M154,'2018 Data'!D5:BC5)))

How do i make the reference to D7:BC7 above dynamic linked to my drop down?

Thanks.

 

[AlphaFrog]

Consider using a SUMIFS function

=SUMIFS(INDEX(C1:BC102,MATCH(Department, B1:B102,0),0), C1:BC1, ">=" & Start_Week, C1:BC1, "<=" & End _Week)

 

[Mark McInerney]

Genius - Just Genius!!

Thanks so much!!!!

 

[AlphaFrog]

You're welcome. Thanks for the feedback.