SUMIF in multiple columns based on other criteria in Excel?

S

Scott Powell

I'm trying to sum the number in one column based on criteria in another
column, but there are also multiple columns on the spreadsheet.

1 5 2 5 1.5 7 2 7


If the second number is a 5, I want the sum of the first numbers. Likewise
for the 7.

Thanks,
Scott
 
J

JulieD

Hi Scott

not actually sure what the criteria is here ... or what you're after
do you want the sum all the values together that are to the left of a 5 or a
7

or do you want to sum the first column if the second column is either a 5 or
7?

or do you want to sum the numbers in column A if B is a 5, in C if D is a 5,
E if F is a 7 and G if H is a 7
 
D

Domenic

I'm not sure if this is what you're looking for, but try the following...

For data contained in one row:

=SUMIF(B1:H1,5,A1:G1)

For data contained in several rows (10 rows in this example):

=SUMIF(B1:H10,5,A1:G10)

Hope this helps!
 
S

Scott Powell

I guess the question was a LITTLE confusing.

Here is the information hopefully a little better explained.

A B C D E F G H I J K L M
N
1 5 1 7 1 5 1 7 1 7 1 7 1
5

If B,D,F,H,J,L,N is a 5, I want the sum of the row values in A,C,E,G,I,K,M.
If B,D,F,H,J,L,N is a 7, then I want to the sum of the row values in
A,C,E,G,I,K,M.

I assume each of these would have to be done in separate cells. The data I
have has more than 2 columns for each occurence, so the criteria is the 5 or
7, and depending on the criteria I want to sum the corresponding values.

I hope that clears up what I'm trying to do a little better. It's not as
easy to explain as I thought it would be.

Thank you.
 
D

Domenic

Try...

For 5 as your criterion...

=SUMPRODUCT(--(MOD(COLUMN(B1:N1)-COLUMN(B1),2)=0),--(B1:N1=5),A1:M1)

For 7 as your criterion...

=SUMPRODUCT(--(MOD(COLUMN(B1:N1)-COLUMN(B1),2)=0),--(B1:N1=7),A1:M1)

Hope this helps!
 
J

JulieD

Hi Scott

still not 100% clear on what you're after but ....
how about this for the 5s
=SUM(IF(B2=5,A2,0),IF(D2=5,C2,0),IF(F2=5,E2,0),IF(H2=5,G2,0),IF(J2=5,I2,0),IF(L2=5,K2,0),IF(N2=5,M2,0))
subsitute 7 in the formula to add up the 7s

(hopefully someone can come up with a neater solution)
 
S

Scott Powell

Worked great.

Thank you so much.

Scott

JulieD said:
Hi Scott

still not 100% clear on what you're after but ....
how about this for the 5s
=SUM(IF(B2=5,A2,0),IF(D2=5,C2,0),IF(F2=5,E2,0),IF(H2=5,G2,0),IF(J2=5,I2,0),IF(L2=5,K2,0),IF(N2=5,M2,0))
subsitute 7 in the formula to add up the 7s

(hopefully someone can come up with a neater solution)

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
Click to expand...
 
J

JulieD

Hi Scott

you're welcome, but Dominic's solution was a much neater & probably better
solution than mine - so you might like to take another look at it

Cheers
JulieD
 
D

Domenic

Hi Julie,

As far as I know, the only advantage in using my formula is if the range
needs to be expanded to include a large number of cells. Otherwise,
your formula is fine. Nothing's wrong with it. :)

Cheers!
 
Top