SUMIF multiple columns

[joe-carter]

I'm trying to sumif for a range. Pretend below is an exceil file with A1=1, and D4=A. I wrote "=SUMIF(D1:D4,"A",A1:C4)". The answer would be "7", or 1+2+2 in the first "A" row plus 1+0+1 in the second "A" row. It worked for a while, but now it won't. The problem is A1:C4, it seems the SUMIF command wants this to be A1:A4. I could make a big sumif command "=SUMIF(D1:D4,"A",A1:A4)+SUMIF(D1:D4,"A",B1:B4)+SUMIF(D1:D4,"A",C1:C4)", but would rather be clever.

1, 2, 2, A
2, 2, 1, B
1, 1, 1, C
1, 0, 1, A

Thanks,

Joe

 

[Marcelo Branco]

Try this

Formula in B10 copied down
=SUMIF($B$1:$E$1,$B$9,INDEX($B$2:$E$6,MATCH($A10,$A$2:$A$6,0),0))

M.

 

[Rugman67]

Yes, thank you. I thought it was going to be an Index/Match. I for the life of me cannot master the Index/Match, I get the vlookup and the hlookup, trouble with the Index/Match.

 

[Marcelo Branco]

Yes, thank you. I thought it was going to be an Index/Match. I for the life of me cannot master the Index/Match, I get the vlookup and the hlookup, trouble with the Index/Match.

See
http://www.contextures.com/xlFunctions03.html

M.

 

[XLGeekery]

See
403 Forbidden

M.

I'm hoping this is the place for some help on a similar problem. I would like to sumif data from multiple columns but to build on this example where there are repeats in the rows (so column a could be a,b,c,a,a,d,e). Also rather than matching the column title I want to make the number of columns vary based on the current month (so include columns B:H if it is July or month 7 and include columns B:M if it is December or Month 12).

So the data may look like below.

I've tried a couple approaches to achieve this.

First I adapted the formula in this thread but realise the match will only return the first instance of "material 1"
=SUM(INDEX($B$2:$H$5,MATCH(A10,$A$2:$A$5,0),0))

Second I tried achieving this with a combination of offset and sumproduct. The formula I have though covers the full 12 months and I don't know how to adapt the index (without using indirect) to only sum months up until specified current month.
=SUMPRODUCT(($A$2:$A$5=$A10)*(OFFSET($B$2:$M$5,0,0,,)))

[TABLE="width: 1768"]
<tbody>[TR]
[TD] Greatly apprecaite any views.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 619"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]jan[/TD]
[TD]feb[/TD]
[TD]mar[/TD]
[TD]apr[/TD]
[TD]may[/TD]
[TD]jun[/TD]
[TD]jul[/TD]
[TD]aug[/TD]
[TD]sep[/TD]
[TD]oct[/TD]
[TD]nov[/TD]
[TD]dec[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Material 2[/TD]
[TD]22[/TD]
[TD]11[/TD]
[TD]69[/TD]
[TD]131[/TD]
[TD]236[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Service[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]23[/TD]
[TD]31[/TD]
[TD]53[/TD]
[TD]3[/TD]
[TD]39[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Material 2[/TD]
[TD]22[/TD]
[TD]11[/TD]
[TD]69[/TD]
[TD]131[/TD]
[TD]236[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Service 3[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]302[/TD]
[TD]514[/TD]
[TD]689[/TD]
[TD]3[/TD]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Current Month[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Material 2[/TD]
[TD="colspan: 3"]954[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Service[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Service 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 619"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe this

B10 copied down
=SUMPRODUCT((COLUMN($B1:$M1)-COLUMN($B1)+1<=B$9)*(A$2:A$5=A10)*B$2:M$5)

M.

 

[Aladin Akyurek]

Equally, in B10 control+shift+enter, not just enter, and copy down:

=SUM(IF($A$2:$A$5=$A10,OFFSET($B$2:$M$5,0,0,,B$9)))

A formula blank or text value would not affect this adversely.