Solved

# Summing selections from a drop down list

Posted on 2013-05-25
341 Views
I have three cells. Each have the same drop down box (list). Each selection within that list is equal to a value (PN1 is 1, PN2 is 2, PN3 is 3..., PN8 is 8).

The fourth cell displays the summation of cells 1-3.

How would a macro be written to accomplish that?

0
Question by:dgd1212
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 14

Accepted Solution

Faustulus earned 500 total points
ID: 39197140
Would something like this work for you?
Range("D4").Value =  WorksheetFunction.Sum(Range("A1:C1"))
Here D4 is the fourth cell you mentioned. If the cells aren't joined you can enumerate the three cells to be summed. WorksheetFunction.Sum(Range("A1,B2,C3"))

If the job is to determine which selection has been made in each of the drop-downs you would need to tell a little more about the controls' nature. The easiest way would be to read the control's ListIndex property. This property returns 0 for the first choice, 1 for the second and so on. Therefore, the value you wish to assign is ListIndex + 1 and you can sum them up in a loop like,
For i = 1 to 3
n = n + Control(i).Listindex + 1
Next i
The trouble is that not all drop-downs have the ListIndex property and, even if the property exists, different types of controls must be addressed using slightly (or grossly) different syntax. If you aren't sure of the type of your control I suggest you post your worksheet with the 3 drop-downs in it.
0

LVL 81

Expert Comment

ID: 39197948
You might use an array-entered formula for your sum instead of using a macro:
=SUM(IFERROR(MATCH(A1:A3,DropdownChoices,0),))

To array-enter a formula:
1. Paste the formula in the formula bar (or click in the formula bar)
2. Hold the Control and Shift keys down
3. Hit Enter, then release all three keys
Excel should respond by adding curly braces { } surrounding your formula. If not, repeat steps 1 to 3.
SumOfDropdownChoicesQ28139167.xlsx
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
###### Suggested Courses
Course of the Month4 days, 6 hours left to enroll