Sumif Function for Horizontal Range

[SRICHARAN]

Hello Experts,

To Put my Question in Simpler terms, Sumif function can be used for a range which is in vertical i want to do a Sumif for Horizontal Data.

in the attached sample workbook in Sheet named "data" from I2:Z2 is the Range for which i want to apply Sumif
now in the sheet named "summary", i have CGST, SGST and IGST in Rows and E,0,5,12,18,28 in Columns Respectively, to match with my Range in data sheet from I2:Z2 i will concatenate the Row Data and Column Data in summary Sheet Say for Example in Summary Sheet,
= Concatenate($A8,E$6) would Give me Result "SGST12" Like wise the rest.
now i want excel to sum only the Values in Data!I:I in the Cell Summary!B7
like wise it Should Sum only Values in Data!Y:Y in cell Workings!F9

i tried to Use Sumif with index Plus match, but couldn't achieve the desired result.
i would be thankful if any Expert over here Solves this and gives the sheet to me.
i have attached the Sheet too.
Thanks in Advance.

 

[NARAYANK991]

Hi ,

See if this is OK. I have used the SUMPRODUCT function.

Narayan

 

[SRICHARAN]

Hi ,

See if this is OK. I have used the SUMPRODUCT function.

Narayan

Dear Narayan,
Thanks as lot for Immediate Response.
This Perfectly Suits my purpose.
Thanks a lot for this Solution.
Looks like SUMPRODUCT Function has more utility than i know.

 

[r2c2]

@SRICHARAN

Interesting question. You can also use INDEX to generate the range (ie column I or J ... or Z based on the tax code, like below:

=SUM(
INDEX(Data!$I$4:$Z$290,,MATCH(Summary!$A7&Summary!B$6,Data!$I$2:$Z$2,0)))

We omit the second parameter (row num) for INDEX, this will force index to return entire column.

See this for more help on INDEX - http://chandoo.org/wp/2013/09/18/index-formula-usage-and-tips/