Not sure where to go for help???

inglestadt

New Member
Joined
Sep 13, 2006
Messages
5
This is hard for me to explain. But here it goes.
A1=Widget1
A2=Left Handed
A3=Red
A4=Mod2
A5=PriceLevel2
A6=Unit Price

A1 through A5 you can select various options (e.g. A1 could Equal any one of the following: Widget1, Widget2, Widget3 or Widget4)

Trying to right a formula where a value could automatically be entered in A6 based on which values are in A1 to A5.

Example 1: A6=$500 if (A1=Widget1, A2=Left Handed, A3=Red, A4=Mod2, 5=PriceLevel2).

A6 would = $400 if ((A1=Widget3, A2=Right Handed, A3=White, A4=Mod2, 5=PriceLevel4).

Any suggestions.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi inglestadt
Welcome to the board

This is a suggestion.
Build a table with all the possible sets of options and the respective value. This is an easy way to keep track of all the options and it's very easy to update.

In this example I built a small table with some combinations of options.

The inputs are entered in A1:A5

The result in A6

Code:
=SUMPRODUCT(--(C2:C25=A1),--(D2:D25=A2),--(E2:E25=A3),--(F2:F25=A4),--(G2:G25=A5),H2:H25)

Hope this helps get you started
PGC
Book1.xls
ABCDEFGHI
1Widget1WidgetRight/LeftHandedColourModPriceLevelValue
2LeftHandedWidget1RighthandedRedMod1PL1948
3BlueWidget1RighthandedRedMod1PL2510
4Mod2Widget1RighthandedRedMod2PL1153
5PL1Widget1RighthandedRedMod2PL2862
6318Widget1RighthandedBlueMod1PL1594
7Widget1RighthandedBlueMod1PL2304
8Widget1RighthandedBlueMod2PL11061
9Widget1RighthandedBlueMod2PL2324
10Widget1LeftHandedRedMod1PL1910
11Widget1LeftHandedRedMod1PL21074
12Widget1LeftHandedRedMod2PL1148
13Widget1LeftHandedRedMod2PL2861
14Widget1LeftHandedBlueMod1PL11082
15Widget1LeftHandedBlueMod1PL21092
16Widget1LeftHandedBlueMod2PL1318
17Widget1LeftHandedBlueMod2PL2336
18Widget2RighthandedRedMod1PL1635
19Widget2RighthandedRedMod1PL2638
20Widget2RighthandedRedMod2PL1416
21Widget2RighthandedRedMod2PL2380
22Widget2RighthandedBlueMod1PL1389
23Widget2RighthandedBlueMod1PL2599
24Widget2RighthandedBlueMod2PL1831
25Widget2RighthandedBlueMod2PL2805
26
Sheet12
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
P. S. You may want Data Validation with lists in A1:A5 to avoid typos or extra spaces
 

inglestadt

New Member
Joined
Sep 13, 2006
Messages
5

ADVERTISEMENT

Having some issues

OK, some luck, but not getting her to work. Did a small sample like yours but the value it is giving back is weird. Value on first sample should be 1 but it is coming back 392???

There is no field in my table with the value 392?

=SUMPRODUCT(--(A47:A246=B15),--(B47:B246=C15),--(C47:C246=D15),--(D47:D246=E15),--(E47:E246=F15)--(F47:F246=G15),--(G47:G246=H15),H47:H246)
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi inglestadt

Your formula has one error. You missed a comma between --(E47:E246=F15) and --(F47:F246=G15).

It should be:

=SUMPRODUCT(--(A47:A246=B15),--(B47:B246=C15),--(C47:C246=D15),--(D47:D246=E15),--(E47:E246=F15),--(F47:F246=G15),--(G47:G246=H15),H47:H246)

Apart from that your formula is OK. The only problem is that you did not post any data is and so I cannot confirm.

Anyway if you still get error here are some remarks:

Your formula looks for:

the value B15 in the range A7:A246,
the value C15 in the range B7:B246,
the value D15 in the range C7:C246,
the value E15 in the range D7:D246,
the value F15 in the range E7:E246,
the value G15 in the range F7:F246,
the value H15 in the range G7:G246,

and when all are TRUE sums the corresponding values in H7:H246.

The only possible problem I can think of is if you have more than one price for the same combination. In that case the formula would sum all the prices corresponding to the same combination. If for a combination you specify you have a first price of 1, a second price of 300 and a third price of 91 the result woukd indeed be 392. However, it doesn't seem logical to me to have more than one price for the same combination (is it?).

If you still get error, a suggestion: start testing with just, for instance, 10 rows, maybe it's easier to catch the error.

=SUMPRODUCT(--(A47:A56=B15),--(B47:B56=C15),--(C47:C56=D15),--(D47:D56=E15),--(E47:E56=F15),--(F47:F56=G15),--(G47:G56=H15),H47:H56)

If this does not work please try to post some data, (maybe 10 rows) and we'll go from there.

To post data:
http://www.mrexcel.com/board2/viewtopic.php?t=92622&sid=f73fd6ff6da4863e89ee609a3ac9cf7b


Hope this helps
PGC
 

inglestadt

New Member
Joined
Sep 13, 2006
Messages
5

ADVERTISEMENT

Many thanks

Finally got back around to working on this. Plugged the comma in and it works like a charm. Have a ton of SKUs to plug in but every test I did work fine.

While getting this to work it brought up another idea, I wasn't sure if Excel could do or not. Using the same concept is it possible to select an item in a list from one cell and that determines or limits what is listed in the following cells.

e.g.

A1 = Company 1, Company 2 or Company 3

if A1 = Company 1 then B2 would list (Widget 1, Widget 2 or Widget 3) however if A1 = Company 3 then B2 would list different list (Gadget A, Gadget B or Gadget C)

Hope this makes sense.
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
See Aladin's explanation here,

http://www.mrexcel.com/board2/viewtopic.php?p=154#154

And as a personal preference, SUMPRODUCT should be used for multi conditional counting/summing. For retrieval look into INDEX/MATCH. A caveat of both solutions would be in case of duplicate records. SUMPRODUCT will sum and INDEX/MATCH will return only the first instance. However, INDEX/MATCH will retrieve both text and numeric, while SUMPRODUCT cannot retrieve text.
 

inglestadt

New Member
Joined
Sep 13, 2006
Messages
5
Making sense of this

thanks Brian,

Read all six pages and think this is what I need will do some samples in a bit. Great detail and info too.
 

Forum statistics

Threads
1,136,878
Messages
5,678,305
Members
419,753
Latest member
Sallylwy

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