Auto calculate based on multiple drop down options

SaraWitch

SaraWitch

Active Member
Joined
Sep 29, 2015
Messages
378
Office Version
  1. 365
Platform
  1. Windows
Hello peeps,

I'm not sure if Excel is capable of this, but remain confident that it can!

I have a multiple drop down validation list. I have another range of cells containing costs. I can work out how to return a value from one selection, but not how to automatically add up the costs for multiple selection.
¦ MrExcel Queries.xlsm
ABCDEFGHIJ
1Capital worksDate given to contractorDate completedCapital works costsCaptial works costs should beCapital WorksCosts
2Air sourced heat pump Bathroom Electric storage heaters Front external door15/05/202401/08/2024£8,000.00£18,020.00Air sourced heat pump£8,000.00
3Gas boiler Radiators21/06/202402/08/2024£3,700.00£6,300.00Bathroom£4,500.00
4Flat entrance door Rear external door07/07/202403/08/2024£4,520.00£6,040.00Electric panel heaters£4,000.00
5Separate WC Level access shower02/08/202404/08/2024£2,530.00Will allow overtype for manual input as LAS costs varyElectric storage heaters£4,000.00
6Flat entrance door£4,520.00
7Front external door£1,520.00
8Gas boiler£3,700.00
9Kitchen£6,200.00
10Level access showerInstall costs
11Radiators£2,600.00
12Rear external door£970.00
13Rewire£1,600.00
14Separate WC£1,300.00
Calculate Multiple DDL
Cell Formulas
RangeFormula
D2:D4D2=IF(A2="","",LOOKUP(A2,CapitalWorks,CapitalCosts))
Named Ranges
NameRefers ToCells
CapitalCosts='Calculate Multiple DDL'!$J$2:$J$14D2:D4
CapitalWorks='Calculate Multiple DDL'!$I$2:$J$14D2:D4
Cells with Data Validation
CellAllowCriteria
A2:A14List=CapitalWorks

Any help would be greatly appreciated! :)
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Amazing! 😃 Thank you, @kvsrinivasamurthy!

It just needs slight tweaking please.
a) I need users to be able to overtype the formula as there will sometimes be a manual input.
b) If cell in column A is blank, return blank result rather than £0.00.
¦ MrExcel Queries.xlsm
ABCDEFGHIJ
1Capital worksDate given to contractorDate completedCapital works costsCaptial works costs should be1st answerQueryCapital WorksCosts
2Air sourced heat pump Bathroom Electric storage heaters Front external door15/05/202401/08/2024£8,000.00£18,020.00£18,020.00#SPILL!Air sourced heat pump£8,000.00
3Gas boiler Radiators21/06/202402/08/2024£3,700.00£6,300.00£6,300.00Bathroom£4,500.00
4Flat entrance door Rear external door07/07/202403/08/2024£4,520.00£5,490.00£5,490.00Electric panel heaters£4,000.00
5Separate WC Level access shower02/08/202404/08/2024£2,530.00Need to allow overtype for manual input as LAS costs vary£1,300.00£2,530.00Electric storage heaters£4,000.00
6(Blank cell)£0.00Flat entrance door£4,520.00
7(Blank cell)£0.00Front external door£1,520.00
8(Blank cell)£0.00Gas boiler£3,700.00
9(Blank cell)£0.00Kitchen£6,200.00
10(Blank cell)£0.00Level access showerInstall costs
11Radiators£2,600.00
12Rear external door£970.00
13Rewire£1,600.00
14Separate WC£1,300.00
Calculate Multiple DDL
Cell Formulas
RangeFormula
F2:F10,G2F2=LET(a,$A$2:$A$10,b,$I$2:$I$14,c,$J$2:$J$14,BYROW(a,LAMBDA(r,SUM(IF(ISNUMBER(FIND(b,r)),c,0)))))
D2:D4D2=IF(A2="","",LOOKUP(A2,CapitalWorks,CapitalCosts))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
CapitalCosts='Calculate Multiple DDL'!$J$2:$J$14F2:G2, D2:D4
CapitalWorks='Calculate Multiple DDL'!$I$2:$J$14D2:D4, F2:G2
Cells with Data Validation
CellAllowCriteria
A2:A14List=CapitalWorks

Thank you so much!
 
Upvote 0
Just remembered that I had previously been given a custom cell format to not show zeros (_-£#,##0.00_-;-£ ##,##0.00_-;;_-@_-), so just need the formula to work with a manual input option...
¦ MrExcel Queries.xlsm
ABCDEFGHIJ
1Capital worksDate given to contractorDate completedCapital works costsCaptial works costs should be1st answerQueryCapital WorksCosts
2Air sourced heat pump Bathroom Electric storage heaters Front external door15/05/202401/08/2024£8,000.00£18,020.00£18,020.00#SPILL!Air sourced heat pump£8,000.00
3Gas boiler Radiators21/06/202402/08/2024£3,700.00£6,300.00£6,300.00Bathroom£4,500.00
4Flat entrance door Rear external door07/07/202403/08/2024£4,520.00£5,490.00£5,490.00Electric panel heaters£4,000.00
5Separate WC Level access shower02/08/202404/08/2024£2,530.00Need to allow overtype for manual input as LAS costs vary£1,300.00£2,530.00Electric storage heaters£4,000.00
6Flat entrance door£4,520.00
7Front external door£1,520.00
8Gas boiler£3,700.00
9Kitchen£6,200.00
10Level access showerInstall costs
11Radiators£2,600.00
12Rear external door£970.00
13Rewire£1,600.00
14Separate WC£1,300.00
Calculate Multiple DDL
Cell Formulas
RangeFormula
F2:F10F2=LET(a,$A$2:$A$10,b,$I$2:$I$14,c,$J$2:$J$14,BYROW(a,LAMBDA(r,SUM(IF(ISNUMBER(FIND(b,r)),c,"")))))
G2G2=LET(a,$A$2:$A$10,b,$I$2:$I$14,c,$J$2:$J$14,BYROW(a,LAMBDA(r,SUM(IF(ISNUMBER(FIND(b,r)),c,0)))))
D2:D4D2=IF(A2="","",LOOKUP(A2,CapitalWorks,CapitalCosts))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
CapitalCosts='Calculate Multiple DDL'!$J$2:$J$14F2:G2, D2:D4
CapitalWorks='Calculate Multiple DDL'!$I$2:$J$14D2:D4, F2:G2
Cells with Data Validation
CellAllowCriteria
A2:A14List=CapitalWorks

Thank you!
 
Upvote 0
1) It is not clear. Explain in detail.
2) Formula
Excel Formula: 
=LET(a,$A$2:$A$5,b,$I$2:$I$14,c,$J$2:$J$14,BYROW(a,LAMBDA(r,IF(r="","",SUM(IF(ISNUMBER(FIND(b,r)),c,0))))))
 
Upvote 0
If you want manual entry in between, Spill over formula cannot be used. Instead enter this formula in D2 then drag down.
Excel Formula: 
=IF($A2="","",SUM(IF(ISNUMBER(FIND($I$2:$I$14,$A2)),$J$2:$J$14,0)))
 
Upvote 0
Solution
Hi SaraWitch use this in Cell D2

=SUM(IFERROR(LOOKUP(TRIM(CLEAN(TEXTSPLIT(A2,"
",0,))),$H2:$H14,$I2:$I4),0))
 

Attachments

  • Autoselect.JPG
    Autoselect.JPG
    64.4 KB · Views: 5
Upvote 1
You must log in or register to reply here.

Forum statistics

Threads
1,226,527
Messages
6,191,568
Members
453,665
Latest member
WaterWorks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top