Determining which formula a cell should use depending on drop-down criteria

**RebeccaD1997** · 01-07-2019, 01:29 PM

Hi Guys,

I’m unsure whether this is at all possible but ideally I would like a sum of a cell to be determined by what drop-down box is selected in another cell… i.e. a different sum range for each drop-down option. Is this do-able?

I have read about array formulas and have managed to get it to work separately E.g. {=SUM(SUMIF(G44,F29,D28:D30))} + {=SUM(SUMIF(G45,F29,D28+D30) (I want to exclude D29 in this instance) but cannot combine the two options as one formula so that it uses either depending on the drop-down selected (F29 being the cell with the data validation, G44 and G45 being the two drop down options).

What’s the best way to work this?

Many thanks,

Becky

**6StringJazzer** · 01-07-2019, 02:41 PM

Welcome to the Forum RebeccaD1997!

It is easy enough to have a formula like this

=IF(F29="Option 1",SUMIF(G44,F29,D28:D30),IF(F29="Option 2",SUMIF(G45,F29,D28,D30),""))

replace Option 1 and Option 2 with the actual values in the dropdown.

Besides that I see some problems in the formula you showed. It might give the answer you are expecting but it is a very roundabout way of getting it. Can you describe what you want that formula to do?

**dflak** · 01-07-2019, 02:41 PM

First of all, you might be able to do this with SUMIFS instead of an array formula or possibly even just plain SUM. We'll have to see how the data is laid out and need a bit more explanation as to where the drop downs come from and what the rule is for determining which drop downs go with which ranges to sum.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**modytrane** · 01-07-2019, 02:45 PM

how about:

=if(F29=G44,sum(D28:D30),sum(D28,D30))

**RebeccaD1997** · 01-09-2019, 10:44 AM

Didn't know about the advanced options! Please find attached worksheet - The idea being if F29 (payment type) is "add to loan", D31 (Revised Mortgage Balance) will add D28:D30 and if it set to "Own Funds", D31 (Revised Mortgage Balance) will only add D28 & D30.

Hope this makes sense!

Many thanks for your help in advance guys.

Becky