Impossible Sumif scenario

[boon-yao.tek]

hi guys,
I have a very unique problem.
The file that i'm working on is quite big and complicated.
So I have build an illustration file here.
impossiblesumif.xlsx

I need to sumif in the report tab.
I am not allowed to make any changes in the source tab.
The source tab uses slightly different name as what I need to show in my report tab.
I have mapped them out in the mapping tab.

I can make changes only in mapping tab and report tab but not the source tab at all.

How can I sumif for different namings when i'm not able to add them (Fruits, Poultry and Vegetable) in the source tab right beside the original names (Orange, Apple, Chicken and etc.?) from the source?

Is there any smart way which I can sumif index match to the mapping table?

My 2nd problem, is for finding the sumrange to sumif with also different namings, For this part, I think I can solve it by using indirect. Is there any other way beside using indirect? if no, then i'm fine too with using indirect to find the range.

(Note: bear in mind, no changes allowed in source tab and no usage of macro.)

Thanks,
Ray
Ray

[JBeaucaire]

Do a little of the work on the MAPPING sheet, then the SUM on the REPORT is easier.

[boon-yao.tek]

ahh, thanks, never thought of such approach to use in mapping sheet.

would like to know if anyone else can give any other suggestion if there's some formula that i can find it out even without precalculating them in mapping sheet.