Conditional Sum of 1 < Columns

[Ben Johnson]

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

 

[Jim Rech]

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

 

[Ben Johnson]

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",B23)

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!

 

[RagDyer]

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

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

Then, try this:

=SUMPRODUCT((A2:A50=E1)*B250)

 

[Marcelo]

Try Sumproduct

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

hth
--

regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Ben Johnson" escreveu:

 

[Ben Johnson]

Perfect...thanks!!!!

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

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

Then, try this:

=SUMPRODUCT((A2:A50=E1)*B250)

 

[RagDyeR]

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