Sumif with multiple columns in sum_range
 

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

Sumif with multiple columns in sum_range
 

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

Sumif with multiple columns in sum_range
 

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

Sumif with multiple columns in sum_range
 

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!

Sumif with multiple columns in sum_range
 

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!

Sumif with multiple columns in sum_range
 

"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 :-)

Sumif with multiple columns in sum_range
 

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

Sumif with multiple columns in sum_range
 

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 :-)

Sumif with multiple columns in sum_range
 

Ron,

You are a godsend! Yes that worked beautifully. I was ready to move on and
try something else but really had no idea.

Again, I really appreciate you sticking with me, trying different things and
such. Helping me to open my mind. I love user groups, a place to give help
and get help.

Thanks again Ron,

Have a great weekend!

AFJr


"Ron Coderre" wrote:

It's time to try another approach:

Try this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER,
instead of ENTER):

=SUM(IF($B$21:$B$62="C",IF(ISNUMBER($Q$21:$S$62),$ Q$21:$S$62)))

Does that help?
-------------------------

Regards,

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

Sumif with multiple columns in sum_range
 

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

Regards,

Ron
Microsoft MVP (Excel)


"AFJr" wrote in message
...
Ron,

You are a godsend! Yes that worked beautifully. I was ready to move on and
try something else but really had no idea.

Again, I really appreciate you sticking with me, trying different things
and
such. Helping me to open my mind. I love user groups, a place to give help
and get help.

Thanks again Ron,

Have a great weekend!

AFJr


"Ron Coderre" wrote:

It's time to try another approach:

Try this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER,
instead of ENTER):

=SUM(IF($B$21:$B$62="C",IF(ISNUMBER($Q$21:$S$62),$ Q$21:$S$62)))

Does that help?
-------------------------

Regards,

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