Index Match to lookup from multiple sheet with condition in row and column

Hi Try this out:

=SUMPRODUCT(SUMIFS(OFFSET(INDIRECT("'"&$G$1:$G$4&"'!C2:C20"),,MONTH(B$12&1)-1,,),INDIRECT("'"&$G$1:$G$4&"'!A2:A20"),$A13,INDIRECT("'"&$G$1:$G$4&"'!B2:B20"),IF($A$10="All","*",$A$10)))

Adjust the end of the range as needed... but do not go mad and add 100,0000 rows if you have 200. Otherwise it'll get slow.

Hi Glenn

Wow..wow.. wow.. Imaging... With such a short formula it provide multiple results that I need. Thank you so much for making my day.


I have done a modification to add an IF function at the end of your formula to get the result with criterial for "MAIN", which is to SUM for those empty cell at Column B in each region sheets.

=SUMPRODUCT(SUMIFS(OFFSET(INDIRECT("'"&$G$1:$G$4&"'!C2:C20"),,MONTH(B$12&1)-1,,),INDIRECT("'"&$G$1:$G$4&"'!A2:A20"),
$A13,INDIRECT("'"&$G$1:$G$4&"'!B2:B20"),IF($A$10="All","*",IF($A$10="MAIN","",$A$10))))

and apply the same formula to work on the 2nd table below for each outlets by selected month. It work perfectly.


I few questions from your formula as below:

(1) When I try to tally back the total for ALL outlets including "Main", your formula ... IF($A$10="A11","*".... does not include the "Main"-empty cell.

What "Wildcard character" should I put that also include empty cell for ALL?

I have done the below formula without using wildcard and it work well too. But I guess my formula may slowdown the calculation process as I have long list around 6000 rows in each source worksheets and for about 20 sources worksheets and 10 calculation sheets in a file. So I still prefer your short formula to reduce the calculation process.

=IF($A$10="ALL",SUMPRODUCT(SUMIF(INDIRECT("'"&$G$1:$G$4&"'!A2:A20"),$A13,OFFSET(INDIRECT("'"&$G$1:$G$4&"'!C2:C20"),,MONTH(B$12&1)-1,,))),
SUMPRODUCT(SUMIFS(OFFSET(INDIRECT("'"&$G$1:$G$4&"'!C2:C20"),,MONTH(B$12&1)-1,,),INDIRECT("'"&$G$1:$G$4&"'!A2:A20"),$A13,
INDIRECT("'"&$G$1:$G$4&"'!B2:B20"),IF($A$10="MAIN","",$A$10))))



(2) The monthly columns format will changed to dd/mm/yyyy (eg: 31/01/2019, 28/02/2019,.... 31/01/2020, 28/02/2020... instate of MMM (eg: JAN, FEB, MAR...) as there will be more columns for months in difference years and also in between the months columns there will have yearly total.


If using OFFSET function =OFFSET(INDIRECT("'"&$G$1:$G$4&"'!C2:C20"),,MONTH(B$12&1)-1,,) it lookup the column by counting the number of column, which will be included the yearly total column. Is there any other better function can be use to match or lookup the column header?


After all the above tested, I found that I don't need to press CTRL+SHIFT+ENTER even with Sumproduct function in the formula. Just Enter it still provide the results correctly.


Looking forward for your solution.

Once again, I would like to thank you so much for your reply to my request.


Shirlena

There's nothing absolutely special about MAIN that will slow things down... I think. I have taken one tiny step out of the formula in B23, copied across and down. I have also added a formula in Row 21 (shaded orange).

I cannpt comment about Q2,as you have added on new columns, I think, which are not on your posted sheet.

Do the IR sheets have to be separate? Can they not all be combined into one, at data import? That would simplify everything!!

It would help if I added the sheet...

Hi Glenn

"MAIN" is consider one of the outlet just it don't come with any prefix code like other outlets at Column B in the individual sheet.
Somehow I still need to put it in the calculation.

Yes it is more simplify if all under one sheet but too bad the source are from separate system database which I cannot combine or edit it.

I have applied all the formula as discussed above into my original financial analysis file and they are working find. Just it will take about 30sec to run the calculation process whenever the selection is changed. However it is fine as long as the results turn out correctly.

I have seen your replied to many requestors and helping them to solve their questions. I am so blissful that I am one of them. You are just imaging and wonderful.

Thanks again Glenn on solving my questions.