Excel drop downs calculate total

Question

Hi I have an excel sheet and have created drop down in text, there are five columns, each drop down has three options-how do I calculate the total based on the drop downs selected (the value is in brackets-but I don't want it to be shown)

eg

column 1

red (1)

blue (2)

green (3)

column 2

apple (1)

orange (2)

banana (3)

column 3

plate (1)

napkin (2)

bag (3)

column 4

small (1)

medium (2)

large (3)

so if I wanted red (1), orange (2), plate (1), large (3) it would show the total in column 5 as 7

Thanks in advance

Answer 1

Thats a really great video but it only works if you type in word excellent, good, average poor etc, is there a way to do it from a drop down list? I can do the drop down lists.

The actually choices on my sheet are more complex and and staff would then need a prompt sheet-which save time from the paper version they are currently using....

Hi,

Given Your requirement/expectation, 1-of-2 things will happen:

1. Either the formula will be long.

OR

2. You will be required to use helper column OR helper grid.

The video shared by Fellow Contributor @Fuad Laguda could be modified to suit Your requirement.

Please check whether the following solution is helpful:

Step 1) Please create a helper column.

In the following screenshot >> I created the helper column in column#F.

Step 2) Formula in cell A3 is: =SUM(MATCH(A1:D1,F2:F13,0)-{0,3,6,9})

NOTE: in the above suggested formula, please change cell ranges to suit Your requirement.

ILLUSTRATION 2 - I chose different options in cells#A1-D1 >> the formula returned the correct result.

If You require further assistance:

Please share screenshots of Your worksheet(s) to help the Contributors understand Your requirement completely & accurately.

If I was able to help You, please mark My response as answer and helpful.

Thank You!

Answer 2

. . .I am almost there, but my spreadsheet has a lot more columns (13) and 5 drop downs

How did you calculate the {0,3,6,9})? . . .

Hi,

e.g. without {0,3,6,9}

- You select Blue in cell A1 - MATCH formula MATCH(A1:D1,F2:F13,0) will return 2

- You select Banana in cell B1 - MATCH formula MATCH(A1:D1,F2:F13,0) will return 6

- You select Plate in cell C1 - MATCH formula MATCH(A1:D1,F2:F13,0) will return 7

- You select Medium in cell D1 - MATCH formula MATCH(A1:D1,F2:F13,0) will return 11

But, when {0,3,6,9} is subtracted from the MATCH formula >> the result is as follows:

- You select Blue in cell A1 - MATCH formula MATCH(A1:D1,F2:F13,0) will return 2-0 = 2

- You select Banana in cell B1 - MATCH formula MATCH(A1:D1,F2:F13,0) will return 6-3 = 3

- You select Plate in cell C1 - MATCH formula MATCH(A1:D1,F2:F13,0) will return 7-6 = 1

- You select Medium in cell D1 - MATCH formula MATCH(A1:D1,F2:F13,0) will return 11-9 = 2

That is why is subtracted {0,3,6,9} to make the adjustment.

Hope, My explanation helped.

Note: after You input the formula >> You may be required to press Ctrl+Shift+Enter keys simultaneously.

Please respond if You require further assistance. I will try My best to be of help.

If I was able to help You, please mark My response as answer and helpful.

Thank You!

Answer 3

You are welcome

Answer 4

Hello Danielle77_580,

To achieve this, watch the video below https://www.youtube.com/watch?v=_GDVE3ic8rU

Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

Answer 5

Thankyou will have a play around with that its really helpful