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 [email protected]> wrote in message
news:[email protected]...
> 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
[email protected]
"Scott Powell" <Scott [email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
Scott Powell <Scott [email protected]> 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

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 [email protected]> wrote in message
> news:[email protected]...
> > 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 <[email protected]>,
Scott Powell <[email protected]> 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 [email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
>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 [email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
> news:[email protected]...
> >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 [email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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 [email protected]> wrote in
>> >> message
>> >> news:[email protected]...
>> >> > 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 <#[email protected]>,
"JulieD" <[email protected]> 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