SUMIF in multiple columns based on other criteria in Excel?

[Guest]

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

 

[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

 

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

 

[Don Guillett]

Huh?

 

[Guest]

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.

 

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

 

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

 

[Guest]

Worked great.

Thank you so much.

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

 

[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

 

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