Using Data from Drop Down Menus in Sum Formulas

G

Guest

Hi there,
I'd like to set up a spreadsheet with numerical drop down menus, the
selected values of which can then be taken and added together using the sum
function. I've tried adding in a drop down using the forms tool bar but sum
function won't seem to add the values.
Can anybody help?
Thanks
Will
 
G

Guest

Think the numbers you are trying to sum are text numbers

Instead of, say: =SUM(I1:I10)

Try, array-entered, ie press CTRL+SHIFT+ENTER
instead of just pressing ENTER:
=SUM(I1:I10+0)
 
G

Guest

Hi Max,
Thanks for that but that still doesn't seem to work. Do you think I might
need to format the numbers in the drop down menu?
What do you reckon?
Will
 
G

Guest

Formatting doesn't change underlying values. I wonder if you did array-enter
the suggested formula correctly? It should work ok. If you did array enter it
correctly you should see that the formula is wrapped with curly braces: { }
in the formula bar. These braces are inserted by Excel. Perhaps try it again?
Just click inside the formula bar where you placed: =SUM(I1:I10+0), then
press CTRL+SHIFT+ENTER. The addition of the zero: +0 should suffice to coerce
the text numbers within I1:I10 to real numbers, but the formula needs to be
array-entered.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating Drop-down menus with subset drop-down menus 4
drop down menus and corresponding hyperlinks 1
Sum from a Drop down menu using value in the cell below 11
Modifying Data in Drop Down Menu 2
how do you use formulas in a drop down box 1
shortcuts to formulas 2
Nesting drop-down menus 1
Calculating the Sum of Drop-down list content control values (vs. display) 1

Top