PDA

View Full Version : [SOLVED] SUMIFS Formula to pull data from next tab



satish gubbi
09-10-2015, 05:59 AM
Hi Team,
I have workbook with two tabs Analysis and Data tab.
Data tab will have details

I would require “=SUMIFS” formula which would pull details based on the Cell B1 and B2, C2 & D2 for respective Names in column A

Ideally, if I change the Month (Cell B1), it should pick the details for particular month from the Data tab.

Kindly help me in this regard

Regards,
Satish Gubbi

Trebor76
09-10-2015, 06:10 PM
Hi Satish Gubbi,

As on my assumption that you need a 3 way lookup, put this formula into cell B3 of the Analysis tab...

=INDEX(Data!$B$3:$J$5,MATCH($A3,Data!$A$3:$A$5,0),MATCH($B$1,Data!$B$1:$J$1 ,0)+MATCH(B$2,Data!$B$2:$J$2,0)-1)

...and copy it across to C3:D3 and then copy the range B3:D3 to B4:D5 (i.e. fill the range B3:D5 with the formula in cell B3).

Note I have created this formula based on the solution from this thread (http://www.mrexcel.com/forum/excel-questions/863072-3-way-lookup-formula.html).

Regards,

Robert

satish gubbi
09-11-2015, 11:51 PM
Hi Trebor76,

Thank you very much for your reply, in fact this did help me solving my query. Appreciate your help in this regard.

As always, I have not returned with empty handed from VBA Express.

Regards.
Satish Gubbi

Trebor76
09-13-2015, 09:43 PM
You're welcome. I'm glad we were able to help :)