how to find relative columns sum in single column on matched basis

[Sukhdev Singh]

Dear All,
Greetings for the day!
Hope all your doing well, I have a sheet with the name of Raw in which there are branch in rows and items are in column, each item has more than one color, but I want to take the sum of only items in report sheet with formula against each branch. I tried to sumif and sumifs both but failed to get the result.
Please help if anyone can solve it. help is appreciated. Sample sheet is attached for your kind perusal.

thanks,
best Rgds,
Sukhdev Singh

 

[NARAYANK991]

Hi ,

As usual your best friend is SUMPRODUCT.

Narayan

 

[Sukhdev Singh]

Thanks a Lot Dear Naraya,
this is the exactly I need. Thanks once again.

Thanks,
Best Rgds,
SUkhdev Singh

 

[Naresh Yadav]

pls find attached excel file

 

[bosco_yip]

Or...........

B2, copy across and down :

=IFERROR(1/(1/SUMIFS(OFFSET(Raw!$A$1,MATCH($A2,Raw!$A$3:$A$12,0)+1,1,1,40),Raw!$B$1:$AO$1,B$1)),"")

Regards
Bosco