Summing with multiple conditions

kaldag

New Member
Joined
Jun 4, 2002
Messages
15
In column A is a discription. In column B is a value. What I want to do is sum the values in column B if the discriptions in column A match as many as two or three discriptions. I have tried a SUM array formula with no results. HELP

Ken
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
On 2002-09-06 13:39, kaldag wrote:
In column A is a discription. In column B is a value. What I want to do is sum the values in column B if the discriptions in column A match as many as two or three discriptions. I have tried a SUM array formula with no results. HELP

Ken

Give a small sample along with the expected result.
 

kaldag

New Member
Joined
Jun 4, 2002
Messages
15
column A Column B

Mon 25
Tues 47
Wed 7412
Tues 45
Mon 58
Wed 47
Wed 45
Mon 47
Tues 45
Wed 4185

What I need is to sum all the values from column B that have Mon and Tues as disc from Column A.

Ken
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
On 2002-09-06 14:01, kaldag wrote:
column A Column B

Mon 25
Tues 47
Wed 7412
Tues 45
Mon 58
Wed 47
Wed 45
Mon 47
Tues 45
Wed 4185

What I need is to sum all the values from column B that have Mon and Tues as disc from Column A.

Ken

=SUMPRODUCT((A1:A10=D1:E1)*(B1:B10))

where D1 houses Mon and E1 Tues, which I assume to be entered as text.
 

grev

New Member
Joined
Sep 4, 2002
Messages
21

ADVERTISEMENT

I have a table with three columns, Customer#, Part # and QTY. I would like to return the Qty value associated with that part# and cust#. The problem is the customer# can be listed several times along with the same part# with differet qty's.
Example:
cust prt# qty
x 123 1
x 123 3
x 456 1
y 123 3
z 123 5
z 123 2

I was using a vlookup for cust# then another lookup for prt# but how do I add & get the sum of the same prt#? x 123 4, z 123 7

Maybe there is an easier way. Thanks for the help.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
On 2002-09-08 08:28, grev wrote:
I have a table with three columns, Customer#, Part # and QTY. I would like to return the Qty value associated with that part# and cust#. The problem is the customer# can be listed several times along with the same part# with differet qty's.
Example:
cust prt# qty
x 123 1
x 123 3
x 456 1
y 123 3
z 123 5
z 123 2

I was using a vlookup for cust# then another lookup for prt# but how do I add & get the sum of the same prt#? x 123 4, z 123 7

Maybe there is an easier way. Thanks for the help.

Let A2:A7 houses cust values, B2:B7 prt#'s, and C2:C7 qty values.

=SUMPRODUCT(($A$2:$A$7=E1)*($B$2:$B$7=F1),$C$2:$C$7)

where E1 houses a condition like "x", a cust value and F1 123, a prt#.
 

grev

New Member
Joined
Sep 4, 2002
Messages
21
thanks for your help it worked great! Have one more question though. How can I return a value where it is in the same column as the cust#. For example:
cust #
x (has values under the same column such as)
a
g
j
How can I retrieve the value j
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
On 2002-09-08 10:42, grev wrote:
thanks for your help it worked great! Have one more question though. How can I return a value where it is in the same column as the cust#. For example:
cust #
x (has values under the same column such as)
a
g
j
How can I retrieve the value j

I don't think I understand the question. What is the retrieval cue...I mean key for picking out "j" -- "j" itself or what?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,337
Messages
5,641,571
Members
417,223
Latest member
jelena_

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top