SUMIF multiple columns

1. ## SUMIF multiple columns

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

2. ## Re: SUMIF multiple columns

Hi,

Maybe

=SUMPRODUCT(\$A\$1:\$C\$4*(\$D\$1:\$D\$4="A"))

HTH

M.

3. ## Re: SUMIF multiple columns

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

Control+shift+enter, not just enter:

=SUM(IF(\$D\$1:\$D\$4="A",\$A\$1:\$C\$4))

Note to Marcelo. The star syntax to over vector/matrix multiplications will run into problems if the matrix reference houses blanks or other text.

4. ## Re: SUMIF multiple columns

=SUMPRODUCT(\$C\$3:\$F\$65,(\$N\$3:\$N\$65="A")) is what I typed in and it worked for a moment, but now it won't, just like the SUMIF.

I actually have (8) columns to add in this matter. I guess the better question is, how can I be more concise with the following formula:
=SUMIF(\$N\$3:\$N\$65,K67,\$C\$3:\$C\$65)+SUMIF(\$N\$3:\$N\$65,K67,\$D\$3:\$D\$65)
+SUMIF(\$N\$3:\$N\$65,K67,\$E\$3:\$E\$65)+SUMIF(\$N\$3:\$N\$65,K67,\$F\$3:\$F\$65)
+SUMIF(\$N\$3:\$N\$65,K67,\$V\$3:\$V\$65)+SUMIF(\$N\$3:\$N\$65,K67,\$W\$3:\$W\$65)
+SUMIF(\$N\$3:\$N\$65,K67,\$X\$3:\$X\$65)+SUMIF(\$N\$3:\$N\$65,K67,\$Y\$3:\$Y\$65).

Notice my (8) columns of C, D, E, F, then V, W, X, Y. Since the criteria is the same (wherever \$N\$3:\$N\$65 equals K67), I should be able to create a more concise formula.

joe

5. ## Re: SUMIF multiple columns

=SUMPRODUCT(\$C\$3:\$F\$65,(\$N\$3:\$N\$65="A")) is what I typed in and it worked for a moment, but now it won't, just like the SUMIF.
Try this

=SUMPRODUCT(\$C\$3:\$F\$65*(\$N\$3:\$N\$65="A"))

M.

6. ## Re: SUMIF multiple columns

Note to Marcelo. The star syntax to over vector/matrix multiplications will run into problems if the matrix reference houses blanks or other text.
You are right, as usual. Tks for for the advice.

M.

7. ## Re: SUMIF multiple columns

Originally Posted by joe-carter
=SUMPRODUCT(\$C\$3:\$F\$65,(\$N\$3:\$N\$65="A")) is what I typed in and it worked for a moment, but now it won't, just like the SUMIF.

I actually have (8) columns to add in this matter. I guess the better question is, how can I be more concise with the following formula:
=SUMIF(\$N\$3:\$N\$65,K67,\$C\$3:\$C\$65)+SUMIF(\$N\$3:\$N\$65,K67,\$D\$3:\$D\$65)
+SUMIF(\$N\$3:\$N\$65,K67,\$E\$3:\$E\$65)+SUMIF(\$N\$3:\$N\$65,K67,\$F\$3:\$F\$65)
+SUMIF(\$N\$3:\$N\$65,K67,\$V\$3:\$V\$65)+SUMIF(\$N\$3:\$N\$65,K67,\$W\$3:\$W\$65)
+SUMIF(\$N\$3:\$N\$65,K67,\$X\$3:\$X\$65)+SUMIF(\$N\$3:\$N\$65,K67,\$Y\$3:\$Y\$65).

Notice my (8) columns of C, D, E, F, then V, W, X, Y. Since the criteria is the same (wherever \$N\$3:\$N\$65 equals K67), I should be able to create a more concise formula.

joe
Try this array formula**:

=SUM(IF(N3:N65=K67,C3:F65),IF(N3:N65=K67,V3:Y65))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

8. ## Re: SUMIF multiple columns

both worked,
{=SUM(IF(\$N\$3:\$N\$65=K67,\$C\$3:\$F\$65))+SUM(IF(\$N\$3:\$N\$65=K67,\$V\$3:\$Y\$65))}
AND
{=SUM(IF(\$N\$3:\$N\$65=K67,\$C\$3:\$F\$65),IF(N3:N65=K67,\$V\$3:\$Y\$65))

I went with the second one. It will hopefully get me thinking about using array formulas more.

Thanks!

9. ## Re: SUMIF multiple columns

Originally Posted by joe-carter
both worked,
{=SUM(IF(\$N\$3:\$N\$65=K67,\$C\$3:\$F\$65))+SUM(IF(\$N\$3:\$N\$65=K67,\$V\$3:\$Y\$65))}
AND
{=SUM(IF(\$N\$3:\$N\$65=K67,\$C\$3:\$F\$65),IF(N3:N65=K67,\$V\$3:\$Y\$65))

I went with the second one. It will hopefully get me thinking about using array formulas more.

Thanks!
You're welcome. Thanks for the feedback!

10. ## Re: SUMIF multiple columns

In cell B10 - B12 I am trying to add the data from the table above A1:E10. In other words in cell B10 I am trying to sum the data in cells B2 to E2, with the criteria of "Jan" and "a". In all the videos on YouTube I only saw sumifs being used for columns with different criteria, here we have the same criteria. I know you could us a simple "sum" but it is very manual especially if the data is on a different worksheet and it span 100+ columns. I hope the question makes sense. Thank you.

## 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
•