Sumifs for Data given with multiple columns

[Surbhi_baphna]

Hi ,

I want to incorporate Sumifs formula where my sum range is dynamic basis the given criteria.

For ex :

In below data, i want to find sum of Sample 2 , for Business A and level 1 as my criteria. and sum should be for all YTD columns, so in case i change June as YTD, sum should include June as well. Pls help, waiting for answer...


[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]YTD[/TD]
[TD="width: 64"]YTD[/TD]
[TD="width: 64"]YTD[/TD]
[TD="width: 64"]YTD[/TD]
[TD="width: 64"]YTD[/TD]
[TD="width: 64"]YTD[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Bus A[/TD]
[TD]Bus A[/TD]
[TD]Bus B[/TD]
[TD]Bus A[/TD]
[TD]Bus A[/TD]
[TD]Bus B[/TD]
[TD]Bus A[/TD]
[TD]Bus A[/TD]
[TD]Bus B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Level 1[/TD]
[TD]Level 2[/TD]
[TD]Level 1[/TD]
[TD]Level 1[/TD]
[TD]Level 2[/TD]
[TD]Level 1[/TD]
[TD]Level 1[/TD]
[TD]Level 2[/TD]
[TD]Level 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Apr[/TD]
[TD]Apr[/TD]
[TD]Apr[/TD]
[TD]May [/TD]
[TD]May [/TD]
[TD]May [/TD]
[TD]June[/TD]
[TD]June[/TD]
[TD]June[/TD]
[/TR]
[TR]
[TD]Sample 1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Sample 2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]Sample 3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Hi. Something like:

=SUMIFS(INDEX($B$5:$J$7,MATCH("Sample 2",$A$5:$A$7,0),0),$B$1:$J$1,"YTD",$B$2:$J$2,"Bus A",$B$3:$J$3,"Level 1")

 

[Surbhi_baphna]

Thanks a lot, this works well