SUMIF multiple columns - Page 2

1. ## Re: SUMIF multiple columns

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.

2. ## Re: SUMIF multiple columns

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.

3. ## Re: SUMIF multiple columns

Originally Posted by 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.

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

M.

4. ## Re: SUMIF multiple columns

Originally Posted by Marcelo Branco

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.

 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

5. ## Re: SUMIF multiple columns

Maybe this

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

M.

6. ## Re: SUMIF multiple columns

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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•