how to sum values from multiple columns based on values

[sanjeev]

Hi
I want to sum the values from multiple columns based on some criteria. And also make a unique list from multiple entries. all I want to do in excel by formulas. Is there any way.
attaching sheet for reference.

 

[Debraj]

Hi Sanjeev,

In H15, use formula as ...
=SUM(SUMIF(OFFSET($B$3:$B$6,,{0,3,6,9}),B13,OFFSET($C$3:$C$6,,{0,3,6,9})))

 

[sanjeev]

Hi Sanjeev,

In H15, use formula as ...
=SUM(SUMIF(OFFSET($B$3:$B$6,,{0,3,6,9}),B13,OFFSET($C$3:$C$6,,{0,3,6,9})))

Hi Devraj
First of all, thanks for prompt reply......
also I want to put values in below table in row 12 under range-1, range-2 range-3 and so on... how can i do it. please advise.

 

[Hui]

C13: =SUMIFS(OFFSET($C$3:$C$5,,MATCH(C$12,$B$1:$L$1,0)-1),OFFSET($B$3:$B$5,,MATCH(C$12,$B$1:$L$1,0)-1),$B13)

Copy across and down

 

[sanjeev]

C13: =SUMIFS(OFFSET($C$3:$C$5,,MATCH(C$12,$B$1:$L$1,0)-1),OFFSET($B$3:$B$5,,MATCH(C$12,$B$1:$L$1,0)-1),$B13)

Copy across and down

Thanks a million...

 

[Debraj]

Or you can try this also..
In C13
=IFERROR(VLOOKUP($B13,OFFSET($B$1,2,MATCH(C$12,$B$1:$L$1,0)-1,4,2),2,0),"")

 

[Sajan]

Hi,
Attached is your workbook modified with formulas to generate the unique colors list, unique range headings, and data values.


Cheers,
Sajan.

 

[sanjeev]

Hi,
Attached is your workbook modified with formulas to generate the unique colors list, unique range headings, and data values.


Cheers,
Sajan.

great Sajan
how could you write such a big formula.... have you done it manually or some other method. Thanks again for your kind response.

 

[Sajan]

Hi Sanjeev,
Unfortunately, I am not aware of any automated ways to write formulas. As such, I write them the old fashioned way... manually. However, I sometimes use some helper cells to write longer formulas. (After I determine how the whole formula is to come together, I test each segment separately, before putting it all together.)

Hope that helps.

-Sajan.