SUMIF in multiple columns based on other criteria in Excel?
 

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

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Scott Powell" <Scott wrote in message
...
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

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!

In article ,
Scott Powell <Scott wrote:

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

Huh?

--
Don Guillett
SalesAid Software

"Scott Powell" <Scott wrote in message
...
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

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.

"JulieD" wrote:

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Scott Powell" <Scott wrote in message
...
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

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!

In article ,
Scott Powell wrote:

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.

"JulieD" wrote:

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Scott Powell" <Scott wrote in message
...
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

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
"Scott Powell" wrote in message
...
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.

"JulieD" wrote:

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Scott Powell" <Scott wrote in message
...
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

Worked great.

Thank you so much.

Scott

"JulieD" wrote:

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
"Scott Powell" wrote in message
...
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.

"JulieD" wrote:

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Scott Powell" <Scott wrote in message
...
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

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
"Scott Powell" wrote in message
...
Worked great.

Thank you so much.

Scott

"JulieD" wrote:

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
"Scott Powell" wrote in message
...
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.

"JulieD" wrote:

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Scott Powell" <Scott wrote in
message
...
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

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!

In article ,
"JulieD" wrote:

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