Conditional Sum of 1 < Columns
 

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

Conditional Sum of 1 < Columns
 

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

Conditional Sum of 1 < Columns
 

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

Conditional Sum of 1 < Columns
 

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

Conditional Sum of 1 < Columns
 

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

Conditional Sum of 1 < Columns
 

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

Conditional Sum of 1 < Columns
 

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