I am struggling with a formula that will do the following:
If cell C4 = "Past 12 Months", sum all of the values in sheet 2 for that UFN
If cell C4 = "Past 6 Months", sum Jan to June in sheet 2 for that UFN
If cell C4 = "Past 3 Months", sum Apr to June in sheet 2 for that UFN
I hope this makes sense! I can get the IF formula part ok but I am struggling to figure out the Vlook up side of things.
test wb.xlsx
I have attached a sample workbook. Formula should be in the Value column (D4).
Thanks!
Try this.....
In D4
and then copy down.
=IF(C4="Past 12 Months",SUM(OFFSET(INDEX(Table1[jan],MATCH(Sheet2!B4,Table1[UFN],0)),,,,12)),IF(C4="Past 6 Months",SUM(OFFSET(INDEX(Table1[jan],MATCH(Sheet2!B4,Table1[UFN],0)),,,,6)),SUM(OFFSET(INDEX(Table1[jan],MATCH(Sheet2!B4,Table1[UFN],0)),,3,,3))))
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Put this in D4 and copy down. VLOOKUP returns only a single value so that's not what you need. This formula will return the string "error" if the value in C4 is none of the three that you listed.
=IF(C4="Past 12 Months",SUM(OFFSET(Table1[[#Headers],[jan]:[dec]],MATCH(B4,Table1[UFN],0),0)),IF(C4="Past 6 Months",SUM(OFFSET(Table1[[#Headers],[jan]:[jun]],MATCH(B4,Table1[UFN],0),0)),IF(C4="Past 3 Months",SUM(OFFSET(Table1[[#Headers],[jan]:[mar]],MATCH(B4,Table1[UFN],0),0)),"error")))
Here is an even shorter formula. The list of strings in LOOKUP must be in exactly the order shown.
=SUM(OFFSET(Table1[[#Headers],[jan]],MATCH(B4,Table1[UFN],0),0,1,LOOKUP(C4,{"Past 12 Months","Past 3 Months","Past 6 Months"},{12,3,6})))
Pl see attached file.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks