2

Good morning everyone. I require some help with my spreadsheet.

As you will see by the screenshot below, i have added drop down boxes with text that i pick based on different combinations. I then have a value for each option that i wish to auto sum when i change the drop down box.

enter image description here So far, countif and sumif have only enabled me to sum up each text induvidually (so each drop down menu that contains a text option selected will read as 1, instead of its preferred value)

I've read a few different threads on here explaining how people have got similar setups to work using sumif adding in each text option in qoutes and adding a value next to it in the formula, but the downside to this is if i wish to change the value of one of my text options (say for example changing small black from 1 to 4) i would then need to change the value in the formula on each seperate sum cell.

This spreadsheet is designed to test the balance of values for a scoring system, so ideally i need a fast easy workflow where i can constantly change the drop down menu combinations and their values on the fly and autosum takes care of the rest.

If there is such a workflow possible i would greatly appreciate your help on the matter.

  • Also just a bit of an update. I've tried to use a new sheet with a vlookup formula to get the values and then sum that way. It does work to get the values, but the autosum will not work because some cells from my dropdown menu are blank and the vlookup shows them as "N/A" so the autosum will not function correctly with an "error" apparent in my vlookup. I've tried adding a value of 0 to the blank refrence but the vlookup still shows them as "N/A" i.imgur.com/3nHfb0o.jpg – Adam from DHL Dec 20 '14 at 19:46
  • I'm sorry I did not get a chance to see your name to the person who replied with a sumif formula. Unfortantly that was not what i wanted to achieve. To clarify, i wish to get cells F13/G13/H13 to auto sum F3:F11/G3:G11/H3:H11 based on the drop down text in each cell via the corrisponding values in cells B3 to C14. Not only that, but i wish to be able to change values via B3 to C14 and simply refresh the data on the speadsheet for the autosum to update, rather than to have F13 to H13 with three formulas that i need to update everytime i wish to change a value. I hope this makes sense. – Adam from DHL Dec 20 '14 at 20:03
1

You need to provide an extra level of processing so each row in F3:F11/G3:G11/H3:H11 is added. This can be done with a SUMPRODUCT wrapper on a SUMIF.

    SUMPRODUCT wrapper on SUMIF

The formula in F13 is,

=SUMPRODUCT(SUMIF($B$3:$B$14,F$3:F$11,$C$3:$C$14))

Fill right as necessary.

|improve this answer|||||
  • Perfect! Thank you so much this is exactly what i required. – Adam from DHL Dec 20 '14 at 21:57
  • @AdamfromDHL - Glad that sorted you out. BTW, providing there are no interfering values below B14:C14, you can also extend the formula ranges to allow room for future entries; e.g. =SUMPRODUCT(SUMIF($B$3:$B$99,F$3:F$11,$C$3:$C$99)) – Jeeped Dec 20 '14 at 22:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.