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,,)))

Greatly apprecaite any views.

<tbody>
</tbody>

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
2	Material 2	22	11	69	131	236	2	6
3	Service	5	5	23	31	53	3	39
4	Material 2	22	11	69	131	236	2	6
5	Service 3	3	5	302	514	689	3	29
6
7
8
9	Current Month	7
10	Material 2	954
11	Service
12	Service 3

<tbody>
</tbody>

<tbody>
</tbody>

 

[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.