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.

Improve this question
2
  • 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, 2014 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, 2014 at 20:03

1 Answer 1

Reset to default
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
2
  • Perfect! Thank you so much this is exactly what i required.
    – Adam from DHL
    Dec 20, 2014 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))
    – user385793
    Dec 20, 2014 at 22:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .