CODEWITHSUNDEEP
excel
omprakash
omprakash

Reputation: 15

How to get the sum where cell reference is got using vlookup

I have a list of names from rows 2 to 10 and Months names in columns from Jan to Dec. There are values in numerics from cell B2 to end. I have a drop down list cell in another sheet containing months names. I want to get the sum of values according to the month selected. For e.g. If June is choosen in drop down list, sum of values from Jan to June for that particular name should be calculated and shown in that cell. I want to get using Sum and vlookup function. Another e.g. if April is choosen, sum of values from Jan to April must be calculated for that particular name. I am providing a link to the images so that you can get a detailed view of my data. https://drive.google.com/folderview?id=1z2tG-jU849BxCVFwHkOEfyYAOa5oatHe

Upvotes: 0

Views: 62

Answers (2)

barry houdini
barry houdini

Reputation: 46341

One option is to use OFFSET function, e.g.

=SUM(OFFSET(B1,MATCH("barry",A2:A10,0),0,1,MATCH("May",B1:M1,0)))

....or if you really want to use VLOOKUP try this:

=SUMPRODUCT(VLOOKUP("barry",A2:M10,ROW(INDIRECT("1:"&MATCH("May",B1:M1,0)))+1,0))

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

INDEX/MATCH would be better suited:

=SUM(INDEX(B:B,MATCH(P2,A:A,0)):INDEX(A:M,MATCH(P2,A:A,0),MATCH(Q2,1:1,0)))

enter image description here

Upvotes: 2

Related Questions