Sumif formula to add rows and columns

[dyanhew]

Hi, please help with the below formula. For the below data I need to add the "ADMIN" expenses total for both the lines for months less than the selected month but if there are 2 "ADMIN" lines I'm not sure how to adjust my formula. The formula I use to add "ADMIN" line is =SUMIF(B1:E1,"<="&B11,INDEX(B2:E8,MATCH(A12,A2:A8,0),0)). Here B11 is March 10 and therefore the answer I get is 550 as the ADMIN expenses total for the first line for the months Jan, Feb and March is 550 but the answer I want is 850 as I want it to add both the ADMIN lines. PLEASE HELP [TABLE="width: 340"]
<tbody>[TR]
[TD][/TD]
[TD]Jan-10
[/TD]
[TD]Feb-10
[/TD]
[TD]Mar-10
[/TD]
[TD]Apr-10
[/TD]
[/TR]
[TR]
[TD]Sales
[/TD]
[TD="align: right"]1000
[/TD]
[TD="align: right"]1500
[/TD]
[TD="align: right"]1200
[/TD]
[TD="align: right"]800
[/TD]
[/TR]
[TR]
[TD]Cost of sales
[/TD]
[TD="align: right"]600
[/TD]
[TD="align: right"]800
[/TD]
[TD="align: right"]1000
[/TD]
[TD="align: right"]500
[/TD]
[/TR]
[TR]
[TD]Gross profit
[/TD]
[TD="align: right"]400
[/TD]
[TD="align: right"]700
[/TD]
[TD="align: right"]200
[/TD]
[TD="align: right"]300
[/TD]
[/TR]
[TR]
[TD]Admin
[/TD]
[TD="align: right"]100
[/TD]
[TD="align: right"]300
[/TD]
[TD="align: right"]150
[/TD]
[TD="align: right"]100
[/TD]
[/TR]
[TR]
[TD]Admin
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]100
[/TD]
[TD="align: right"]150
[/TD]
[TD="align: right"]200
[/TD]
[/TR]
[TR]
[TD]Selling
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]100
[/TD]
[TD="align: right"]150
[/TD]
[TD="align: right"]50
[/TD]
[/TR]
[TR]
[TD]Net profit
[/TD]
[TD="align: right"]250
[/TD]
[TD="align: right"]300
[/TD]
[TD="align: right"]-100
[/TD]
[TD="align: right"]150
[/TD]
[/TR]
</tbody>[/TABLE]

 

[XOR LX]

Hi,

Try:

=SUMPRODUCT((A2:A8="Admin")*(B1:E1<=$B$11)*B2:E8)

Regards

 

[dyanhew]

Thanks a lot XOR LX. It works

 

[Michael M]

Try

=SUMPRODUCT((B1:E1<=B11)*B2:E8*(A2:A8="Admin"))

.....Darn, too quick XOR...

 

[XOR LX]

".....Darn, too quick XOR..."

Sorry!