Hi all,

I would like to sum more than one column on a given row that matches my
critera.

My data is set up with sales information by style/store like this:
A B C D
1 STYLE# STORE 1 STORE 2 STORE 3
2 0001 13 11 8
3 0002 5 7 4

I'd like to find the Total Sales (Store 1 + Store 2 + Store 3) for a given
Style #

I've tried entering indicating more than one column in the sum range:

SUMIF(A2:A3,"0001",B2:C3)

From my reading of Excel help it seems like this should work but it is only
adding numbers in the first column of my range; am I doing something wrong?

I can't change the file that I'm pulling the data from, so adding a total
column and then pointing to that wouldn't work.

I could add an intermediate file with the totals for each style and point to
that but I feel like there has to be a better way...

Any ideas would be appreciated.

Thanks!!!

The criteria range and the sum range in SUMIF are assumed to have the same
number of rows and columns. Since your criteria range (A2:A3) has one
column only one column of the sum range is summed.

--
Jim
"Ben Johnson" <Ben wrote in message
...
| Hi all,
|
| I would like to sum more than one column on a given row that matches my
| critera.
|
| My data is set up with sales information by style/store like this:
| A B C D
| 1 STYLE# STORE 1 STORE 2 STORE 3
| 2 0001 13 11 8
| 3 0002 5 7 4
|
| I'd like to find the Total Sales (Store 1 + Store 2 + Store 3) for a given
| Style #
|
| I've tried entering indicating more than one column in the sum range:
|
| SUMIF(A2:A3,"0001",B2:C3)
|
| From my reading of Excel help it seems like this should work but it is
only
| adding numbers in the first column of my range; am I doing something
wrong?
|
| I can't change the file that I'm pulling the data from, so adding a total
| column and then pointing to that wouldn't work.
|
| I could add an intermediate file with the totals for each style and point
to
| that but I feel like there has to be a better way...
|
| Any ideas would be appreciated.
|
| Thanks!!!

Thanks so much for your help Jim.

I modified my formula to include the same number of columns in my criteria
range as my sum-range, example:

SUMIF(A2:C3,"0001",B2:D3)

I'm not actually looking for criteria in column B or C as all my style
numbers are in column A.

I hoped that expanding the criteria range, even unnecessarily, would expand
the sum range...unfortunately I'm still getting the same result as before,
first column only...

Did I not understand your advice correctly?

As a side note, I actually have about 10,000 rows and 100 columns so I'm not
able to individually add the columns via VLOOKLUP, hence the SUMIF idea...

Thanks again!


"Jim Rech" wrote:

The criteria range and the sum range in SUMIF are assumed to have the same
number of rows and columns. Since your criteria range (A2:A3) has one
column only one column of the sum range is summed.

--
Jim
"Ben Johnson" <Ben wrote in message
...
| Hi all,
|
| I would like to sum more than one column on a given row that matches my
| critera.
|
| My data is set up with sales information by style/store like this:
| A B C D
| 1 STYLE# STORE 1 STORE 2 STORE 3
| 2 0001 13 11 8
| 3 0002 5 7 4
|
| I'd like to find the Total Sales (Store 1 + Store 2 + Store 3) for a given
| Style #
|
| I've tried entering indicating more than one column in the sum range:
|
| SUMIF(A2:A3,"0001",B2:C3)
|
| From my reading of Excel help it seems like this should work but it is
only
| adding numbers in the first column of my range; am I doing something
wrong?
|
| I can't change the file that I'm pulling the data from, so adding a total
| column and then pointing to that wouldn't work.
|
| I could add an intermediate file with the totals for each style and point
to
| that but I feel like there has to be a better way...
|
| Any ideas would be appreciated.
|
| Thanks!!!

Say your datalist was A1 to D50,
with headers in Row 1, and data in A2:D50.

Assign E1 as the cell where you enter the style number you're looking to
total.

Then, try this:

=SUMPRODUCT((A2:A50=E1)*B2:D50)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Ben Johnson" wrote in message
...
Thanks so much for your help Jim.

I modified my formula to include the same number of columns in my criteria
range as my sum-range, example:

SUMIF(A2:C3,"0001",B2:D3)

I'm not actually looking for criteria in column B or C as all my style
numbers are in column A.

I hoped that expanding the criteria range, even unnecessarily, would
expand
the sum range...unfortunately I'm still getting the same result as before,
first column only...

Did I not understand your advice correctly?

As a side note, I actually have about 10,000 rows and 100 columns so I'm
not
able to individually add the columns via VLOOKLUP, hence the SUMIF idea...

Thanks again!


"Jim Rech" wrote:

The criteria range and the sum range in SUMIF are assumed to have the
same
number of rows and columns. Since your criteria range (A2:A3) has one
column only one column of the sum range is summed.

--
Jim
"Ben Johnson" <Ben wrote in message
...
| Hi all,
|
| I would like to sum more than one column on a given row that matches my
| critera.
|
| My data is set up with sales information by style/store like this:
| A B C D
| 1 STYLE# STORE 1 STORE 2 STORE 3
| 2 0001 13 11 8
| 3 0002 5 7 4
|
| I'd like to find the Total Sales (Store 1 + Store 2 + Store 3) for a
given
| Style #
|
| I've tried entering indicating more than one column in the sum range:
|
| SUMIF(A2:A3,"0001",B2:C3)
|
| From my reading of Excel help it seems like this should work but it is
only
| adding numbers in the first column of my range; am I doing something
wrong?
|
| I can't change the file that I'm pulling the data from, so adding a
total
| column and then pointing to that wouldn't work.
|
| I could add an intermediate file with the totals for each style and
point
to
| that but I feel like there has to be a better way...
|
| Any ideas would be appreciated.
|
| Thanks!!!

Try Sumproduct

SUMPRODUCT(--(A3:A4=$A$3),(B3:B4))+SUMPRODUCT(--(A3:A4=$A$3),(C3:C4))+SUMPRODUCT(--(A3:A4=$A$3),(D3:D4))

hth
--

regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Ben Johnson" escreveu:

Thanks so much for your help Jim.

I modified my formula to include the same number of columns in my criteria
range as my sum-range, example:

SUMIF(A2:C3,"0001",B2:D3)

I'm not actually looking for criteria in column B or C as all my style
numbers are in column A.

I hoped that expanding the criteria range, even unnecessarily, would expand
the sum range...unfortunately I'm still getting the same result as before,
first column only...

Did I not understand your advice correctly?

As a side note, I actually have about 10,000 rows and 100 columns so I'm not
able to individually add the columns via VLOOKLUP, hence the SUMIF idea...

Thanks again!


"Jim Rech" wrote:

The criteria range and the sum range in SUMIF are assumed to have the same
number of rows and columns. Since your criteria range (A2:A3) has one
column only one column of the sum range is summed.

--
Jim
"Ben Johnson" <Ben wrote in message
...
| Hi all,
|
| I would like to sum more than one column on a given row that matches my
| critera.
|
| My data is set up with sales information by style/store like this:
| A B C D
| 1 STYLE# STORE 1 STORE 2 STORE 3
| 2 0001 13 11 8
| 3 0002 5 7 4
|
| I'd like to find the Total Sales (Store 1 + Store 2 + Store 3) for a given
| Style #
|
| I've tried entering indicating more than one column in the sum range:
|
| SUMIF(A2:A3,"0001",B2:C3)
|
| From my reading of Excel help it seems like this should work but it is
only
| adding numbers in the first column of my range; am I doing something
wrong?
|
| I can't change the file that I'm pulling the data from, so adding a total
| column and then pointing to that wouldn't work.
|
| I could add an intermediate file with the totals for each style and point
to
| that but I feel like there has to be a better way...
|
| Any ideas would be appreciated.
|
| Thanks!!!

Perfect...thanks!!!!

"RagDyer" wrote:

Say your datalist was A1 to D50,
with headers in Row 1, and data in A2:D50.

Assign E1 as the cell where you enter the style number you're looking to
total.

Then, try this:

=SUMPRODUCT((A2:A50=E1)*B2:D50)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Ben Johnson" wrote in message
...
Thanks so much for your help Jim.

I modified my formula to include the same number of columns in my criteria
range as my sum-range, example:

SUMIF(A2:C3,"0001",B2:D3)

I'm not actually looking for criteria in column B or C as all my style
numbers are in column A.

I hoped that expanding the criteria range, even unnecessarily, would
expand
the sum range...unfortunately I'm still getting the same result as before,
first column only...

Did I not understand your advice correctly?

As a side note, I actually have about 10,000 rows and 100 columns so I'm
not
able to individually add the columns via VLOOKLUP, hence the SUMIF idea...

Thanks again!


"Jim Rech" wrote:

The criteria range and the sum range in SUMIF are assumed to have the
same
number of rows and columns. Since your criteria range (A2:A3) has one
column only one column of the sum range is summed.

--
Jim
"Ben Johnson" <Ben wrote in message
...
| Hi all,
|
| I would like to sum more than one column on a given row that matches my
| critera.
|
| My data is set up with sales information by style/store like this:
| A B C D
| 1 STYLE# STORE 1 STORE 2 STORE 3
| 2 0001 13 11 8
| 3 0002 5 7 4
|
| I'd like to find the Total Sales (Store 1 + Store 2 + Store 3) for a
given
| Style #
|
| I've tried entering indicating more than one column in the sum range:
|
| SUMIF(A2:A3,"0001",B2:C3)
|
| From my reading of Excel help it seems like this should work but it is
only
| adding numbers in the first column of my range; am I doing something
wrong?
|
| I can't change the file that I'm pulling the data from, so adding a
total
| column and then pointing to that wouldn't work.
|
| I could add an intermediate file with the totals for each style and
point
to
| that but I feel like there has to be a better way...
|
| Any ideas would be appreciated.
|
| Thanks!!!

You're welcome, and thank you for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Ben Johnson" wrote in message
...
Perfect...thanks!!!!

"RagDyer" wrote:

Say your datalist was A1 to D50,
with headers in Row 1, and data in A2:D50.

Assign E1 as the cell where you enter the style number you're looking to
total.

Then, try this:

=SUMPRODUCT((A2:A50=E1)*B2:D50)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Ben Johnson" wrote in message
...
Thanks so much for your help Jim.

I modified my formula to include the same number of columns in my
criteria
range as my sum-range, example:

SUMIF(A2:C3,"0001",B2:D3)

I'm not actually looking for criteria in column B or C as all my style
numbers are in column A.

I hoped that expanding the criteria range, even unnecessarily, would
expand
the sum range...unfortunately I'm still getting the same result as
before,
first column only...

Did I not understand your advice correctly?

As a side note, I actually have about 10,000 rows and 100 columns so I'm
not
able to individually add the columns via VLOOKLUP, hence the SUMIF
idea...

Thanks again!


"Jim Rech" wrote:

The criteria range and the sum range in SUMIF are assumed to have the
same
number of rows and columns. Since your criteria range (A2:A3) has one
column only one column of the sum range is summed.

--
Jim
"Ben Johnson" <Ben wrote in message
...
| Hi all,
|
| I would like to sum more than one column on a given row that matches
my
| critera.
|
| My data is set up with sales information by style/store like this:
| A B C D
| 1 STYLE# STORE 1 STORE 2 STORE 3
| 2 0001 13 11 8
| 3 0002 5 7 4
|
| I'd like to find the Total Sales (Store 1 + Store 2 + Store 3) for a
given
| Style #
|
| I've tried entering indicating more than one column in the sum range:
|
| SUMIF(A2:A3,"0001",B2:C3)
|
| From my reading of Excel help it seems like this should work but it
is
only
| adding numbers in the first column of my range; am I doing something
wrong?
|
| I can't change the file that I'm pulling the data from, so adding a
total
| column and then pointing to that wouldn't work.
|
| I could add an intermediate file with the totals for each style and
point
to
| that but I feel like there has to be a better way...
|
| Any ideas would be appreciated.
|
| Thanks!!!