Hello,

I have a set of data that has a label in column A the sales in column
B, C & D:

Column A Column B Column C
Joel 500 677
Joel 575 752
Joel 650 827
Claudia 725 902
Tarzan 800 968

I'd like to run SUMIF where the "Range" is column A, the "Criteria" is
Joel, and the "Sum_Range" is B:C but when I use SUMIF(A:A,"Joel",B:C)
I only get the sum of column B which is 1,725.

Can someone hook me up with a formula that can sum both column B & C
in this situation.

Thanks, in advance

Daniel

Try this approach:

=SUMPRODUCT((A2:A10="Joel")*B2:C10)

Adjust range references to suit your situation.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"dza7" wrote in message
...
Hello,

I have a set of data that has a label in column A the sales in column
B, C & D:

Column A Column B Column C
Joel 500 677
Joel 575 752
Joel 650 827
Claudia 725 902
Tarzan 800 968

I'd like to run SUMIF where the "Range" is column A, the "Criteria" is
Joel, and the "Sum_Range" is B:C but when I use SUMIF(A:A,"Joel",B:C)
I only get the sum of column B which is 1,725.

Can someone hook me up with a formula that can sum both column B & C
in this situation.

Thanks, in advance

Daniel

On Mar 14, 11:14 am, "Ron Coderre"
wrote:
Try this approach:

=SUMPRODUCT((A2:A10="Joel")*B2:C10)

Adjust range references to suit your situation.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"dza7" wrote in message

...

Hello,

I have a set of data that has a label in column A the sales in column
B, C & D:

Column A Column B Column C
Joel 500 677
Joel 575 752
Joel 650 827
Claudia 725 902
Tarzan 800 968

I'd like to run SUMIF where the "Range" is column A, the "Criteria" is
Joel, and the "Sum_Range" is B:C but when I use SUMIF(A:A,"Joel",B:C)
I only get the sum of column B which is 1,725.

Can someone hook me up with a formula that can sum both column B & C
in this situation.

Thanks, in advance

Daniel

Perfect, thank you very much!

You're very welcome....I'm glad I could help.

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"dza7" wrote in message
...
On Mar 14, 11:14 am, "Ron Coderre"
wrote:
Try this approach:

=SUMPRODUCT((A2:A10="Joel")*B2:C10)

Adjust range references to suit your situation.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"dza7" wrote in message

...

Hello,

I have a set of data that has a label in column A the sales in column
B, C & D:

Column A Column B Column C
Joel 500 677
Joel 575 752
Joel 650 827
Claudia 725 902
Tarzan 800 968

I'd like to run SUMIF where the "Range" is column A, the "Criteria" is
Joel, and the "Sum_Range" is B:C but when I use SUMIF(A:A,"Joel",B:C)
I only get the sum of column B which is 1,725.

Can someone hook me up with a formula that can sum both column B & C
in this situation.

Thanks, in advance

Daniel

Perfect, thank you very much!

"Ron Coderre" wrote in message
...
Try this approach:

=SUMPRODUCT((A2:A10="Joel")*B2:C10)

I can never get my head round the SUMPRODUCT function. Can anyone explain in
very simple terms(!!!) what it does :-)

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

gavin wrote:

"Ron Coderre" wrote in message
...
Try this approach:

=SUMPRODUCT((A2:A10="Joel")*B2:C10)

I can never get my head round the SUMPRODUCT function. Can anyone explain in
very simple terms(!!!) what it does :-)

--

Dave Peterson

For a simple description, type the word SUMPRODUCT into Excel help. For
more detail, type the words SUMPRODUCT and Excel into Google.
--
David Biddulph

"gavin" wrote in message
. ..

"Ron Coderre" wrote in message
...
Try this approach:

=SUMPRODUCT((A2:A10="Joel")*B2:C10)

I can never get my head round the SUMPRODUCT function. Can anyone explain
in very simple terms(!!!) what it does :-)

I see that Dave Peterson gave you the xldynamic link, which is very
comprehensive.

To augment that a bit, I'll describe the SUMPRODUCT functionality
as it applies to formula in this thread.

Let's see if this helps:

SUMPRODUCT was *designed* to multiply two or more same-size
ranges of cells and sum the products.

Example (in cells A1:B4):
Sale Rate
100 0.10
200 0.15
300 0.20

The commission would be the sum of each Sale x Rate.
+(100 x 0.10)
+(200 x 0.15)
+(300 x 0.20)

In this instance, the total happens to be 100.

The SUMPRODUCT representation of that could be:
=SUMPRODUCT(A2:A4,B2:B4)

But SUMPRODUCT has an alternate structure, too:
=SUMPRODUCT((A2:A4)*(B2:B4))

(Sometimes the second one works where the
first one fails and vice versa)

The function multiplies the
A2 x B2 calcs 10.....adds to the total
A3 x B3 calcs 30.....adds to the total
A4 x B4 calcs 60.....adds to the total
Commission = 100 (10+30+60)

*********************************
Before continuing...Let's talk a bit about Boolean Values.
That's a fancy term for True/False values.

This formula:
A1: =(5=5)
returns TRUE....5 DOES equal 5

and
This formula:
A2: =(5=2)
returns FALSE....5 does NOT equal 2

As such, TRUE and FALSE...are treated as WORDS by Excel.
B1: =A1....returns TRUE
and
B2: =A2....returns FALSE

BUT...When you apply an arithmetic operator (+, -, *, /)
to a Boolean Value Excel converts TRUE to 1 and FALSE to 0.

So...
B1: =A1*1.....returns 1, instead of TRUE.
B2: =A2*1.....returns 0, instead of FALSE.

Actually, multiplying by any number converts
TRUE to 1 and FALSE to 0.

So...this would happen, too:
B1: =A1*10.....returns 10.
B2: =A2*10.....returns 0.

**********************************
We'll exploit that feature in SUMPRODUCT.....

In this formula:
=SUMPRODUCT((A2:A10="Joel")*B2:C10)

This expression:
(A2:A10="Joel")....returns a series of TRUE/FALSE values
depending on whether the cell equals "Joel" or not.
and
B2:C10 contains NUMBERS!
SO..
Each TRUE when multiplied by its associated NUMBER
returns that number.

Each FALSE when multiplied by its associated NUMBER
returns converts to a zero...0 x number = 0.

SUMPRODUCT returns the sum of all those results
which is the sum of all combinations where
Col_A="Joel"

Experiment with simple examples until you get
comfortable with the way it works.

I hope that helps.
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"gavin" wrote in message
. ..

"Ron Coderre" wrote in message
...
Try this approach:

=SUMPRODUCT((A2:A10="Joel")*B2:C10)

I can never get my head round the SUMPRODUCT function. Can anyone explain
in very simple terms(!!!) what it does :-)

"Ron Coderre" wrote in message
...
I see that Dave Peterson gave you the xldynamic link, which is very
comprehensive.

To augment that a bit, I'll describe the SUMPRODUCT functionality
as it applies to formula in this thread.

Let's see if this helps:

SUMPRODUCT was *designed* to multiply two or more same-size
ranges of cells and sum the products.

Example (in cells A1:B4):
Sale Rate
100 0.10
200 0.15
300 0.20

The commission would be the sum of each Sale x Rate.
+(100 x 0.10)
+(200 x 0.15)
+(300 x 0.20)

In this instance, the total happens to be 100.

The SUMPRODUCT representation of that could be:
=SUMPRODUCT(A2:A4,B2:B4)

But SUMPRODUCT has an alternate structure, too:
=SUMPRODUCT((A2:A4)*(B2:B4))

(Sometimes the second one works where the
first one fails and vice versa)

The function multiplies the
A2 x B2 calcs 10.....adds to the total
A3 x B3 calcs 30.....adds to the total
A4 x B4 calcs 60.....adds to the total
Commission = 100 (10+30+60)

*********************************
Before continuing...Let's talk a bit about Boolean Values.
That's a fancy term for True/False values.

This formula:
A1: =(5=5)
returns TRUE....5 DOES equal 5

and
This formula:
A2: =(5=2)
returns FALSE....5 does NOT equal 2

As such, TRUE and FALSE...are treated as WORDS by Excel.
B1: =A1....returns TRUE
and
B2: =A2....returns FALSE

BUT...When you apply an arithmetic operator (+, -, *, /)
to a Boolean Value Excel converts TRUE to 1 and FALSE to 0.

So...
B1: =A1*1.....returns 1, instead of TRUE.
B2: =A2*1.....returns 0, instead of FALSE.

Actually, multiplying by any number converts
TRUE to 1 and FALSE to 0.

So...this would happen, too:
B1: =A1*10.....returns 10.
B2: =A2*10.....returns 0.

**********************************
We'll exploit that feature in SUMPRODUCT.....

In this formula:
=SUMPRODUCT((A2:A10="Joel")*B2:C10)

This expression:
(A2:A10="Joel")....returns a series of TRUE/FALSE values
depending on whether the cell equals "Joel" or not.
and
B2:C10 contains NUMBERS!
SO..
Each TRUE when multiplied by its associated NUMBER
returns that number.

Each FALSE when multiplied by its associated NUMBER
returns converts to a zero...0 x number = 0.

SUMPRODUCT returns the sum of all those results
which is the sum of all combinations where
Col_A="Joel"

Experiment with simple examples until you get
comfortable with the way it works.

I hope that helps.
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


Oh no! I didn't understand a word of that - and I thought I was reasonably
intelligent :-(

Hi Ron,

I wanted to thank you for your GREAT explanation:

**********************************
We'll exploit that feature in SUMPRODUCT.....

In this formula:
=SUMPRODUCT((A2:A10="Joel")*B2:C10)

This expression:
(A2:A10="Joel")....returns a series of TRUE/FALSE values
depending on whether the cell equals "Joel" or not.
and
B2:C10 contains NUMBERS!
SO..
Each TRUE when multiplied by its associated NUMBER
returns that number.

Each FALSE when multiplied by its associated NUMBER
returns converts to a zero...0 x number = 0.

SUMPRODUCT returns the sum of all those results
which is the sum of all combinations where
Col_A="Joel"

My question is this, I'm using Excel97, does this function work in this
version?

My formula
=SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62)
is not working. I thought I understood exactly what you were saying, maybe
not.....


--
TIA

AFJr

Hi, AF Jr

If you're not getting a #NAME! error,
then Excel 97 has the SUMPRODUCT
function.

There are 2 variations of the structure.
If this one doesn't work:
=SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62)

Try this one:
=SUMPRODUCT(--($B$21:$B$62="C"),$Q$21:$S$62)

Does that help?
Post back if you have more questions.

Oh, and thanks for the feedback on the explanation.
Much appreciated.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)




"AFJr" wrote in message
...
Hi Ron,

I wanted to thank you for your GREAT explanation:

**********************************
We'll exploit that feature in SUMPRODUCT.....

In this formula:
=SUMPRODUCT((A2:A10="Joel")*B2:C10)

This expression:
(A2:A10="Joel")....returns a series of TRUE/FALSE values
depending on whether the cell equals "Joel" or not.
and
B2:C10 contains NUMBERS!
SO..
Each TRUE when multiplied by its associated NUMBER
returns that number.

Each FALSE when multiplied by its associated NUMBER
returns converts to a zero...0 x number = 0.

SUMPRODUCT returns the sum of all those results
which is the sum of all combinations where
Col_A="Joel"

My question is this, I'm using Excel97, does this function work in this
version?

My formula
=SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62)
is not working. I thought I understood exactly what you were saying, maybe
not.....


--
TIA

AFJr

On Mar 14, 11:10*am, dza7 wrote:
Hello,

I have a set of data that has a label in column A the sales in column
B, C & D:

Column A * * * *Column B * * * *Column C
Joel * * * * * * * *500 * * * * * * * * *677
Joel * * * * * * * *575 * * * * * * * * *752
Joel * * * * * * * *650 * * * * * * * * *827
Claudia * * * * * 725 * * * * * * * * *902
Tarzan * * * * * *800 * * * * * * * * *968

I'd like to run SUMIF where the "Range" is column A, the "Criteria" is
Joel, and the "Sum_Range" is B:C but when I use SUMIF(A:A,"Joel",B:C)
I only get the sum of column B which is 1,725.

Can someone hook me up with a formula that can sum both column B & C
in this situation.

Thanks, in advance

Daniel

something something