Need help totaling based on selections

**BrianHenson** · 03-08-2024, 05:50 PM

Mix ID Calculator.xlsx
I need help from the experts. First of all new to this forum. Thank you for your time. I have attached a snapshot of a table that I made to compute a total cost of a mix ID base off the base price, add-ons selected (multiple can be added) and the distance. I would like the cost of the mix ID to be based off the base price which changes on occasion. The snapshot only shows a sample of data. The COM301510 is the base mix reflecting the base price. So for example:

I select COM351510. If the base price is $121, add + $5 because COM351510 is selected which = $126
I want to add 'Air' to the COM351510. $126 + $3 = $129. I also want to add 'HRWR'. $129 + $5 = $134
Lastly, I add the distance which in this case is 11-20 miles. $134 + $5 = $139 would display in the Total cell.

The add-on cell will need to be checkboxes so that more then one add-on can be selected if needed. Also, I need the prices to be dynamic because they change on occasion as well.

Thank you for any feedback you can provide.

Brian

**AliGW** · 03-09-2024, 03:01 AM

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

**Estevaoba** · 03-09-2024, 02:18 PM

I use a combination of LOOKUP and SUMIF formulas to total sum:

Formula:

Please Login or Register  to view this content.

Range T7:T11 is used for the selection boxes
Please check if this works for you.

Good luck!

**BrianHenson** · 03-11-2024, 09:45 AM

Thanks Estevaoba. How can I add values to columns such as MixID and Distance if I want to expand my options?

**Estevaoba** · 03-11-2024, 03:11 PM

You're welcome.

Please update your sample with the additions you have in mind.

**BrianHenson** · 03-12-2024, 09:49 AM

Estevaoba, here are the updated additions in Mix ID Calculator-BDH.xlsx. Thank you.

**DJunqueira** · 03-12-2024, 10:50 AM

You could try this and named tables.

Formula:

Please Login or Register  to view this content.

**Estevaoba** · 03-12-2024, 10:59 AM

Hi.

I have updated the ranges in the formula to go down to row 100, but you can tweak it as required.

I hope this helps.

**BrianHenson** · 03-12-2024, 11:20 AM

Thank you both. I will look at each of your examples and get back to you. I appreciate your expertise.

**BrianHenson** · 03-12-2024, 04:47 PM

DJunqueira, I was wondering if you could modify the way column A works. I have attached a revised spreadsheet that hopefully you can understand. Basically instead of putting a currency value in A7, I want to have a dropdown with T1, T2 and T3. When I select T1, the value indicated in cell Q5 displays in A7. Likewise, if I select T2 the value in Q6 will be displayed in A7. Same for T3, the value in Q7 will display in A7 when selected. The new spreadsheet I uploaded is addtier.xlsx. Thank you again for the help.

**DJunqueira** · 03-13-2024, 11:46 AM

The file didn't upload.

**BrianHenson** · 03-13-2024, 05:29 PM

DJunqueria here is the file....

**wk9128** · 03-13-2024, 08:49 PM

Cell N7 formula

Formula:

Please Login or Register  to view this content.

**DJunqueira** · 03-14-2024, 01:50 AM

Ok, I added the Tier and a new tab with the price tables.

Formula:

Please Login or Register  to view this content.

**BrianHenson** · 03-14-2024, 09:08 AM

That is exactly what I was looking for. Thanks. I have attached another revision where I duplicated the two tabs and relabeled them for a residential need. Unfortunately, the residential selection tab is pulling from the commercial price tab and not the residential price tab. What am I needing to change?

**DJunqueira** · 03-14-2024, 09:20 PM

Ok, done.
Don't leave the tables with blank rows.
All the tables and consequently formulas for Residential now end with Res.

Formula:

Please Login or Register  to view this content.

**wk9128** · 03-14-2024, 10:24 PM

worksheet or Tab name : Commercial Selection Tab

Cell N7 formula

Formula:

Please Login or Register  to view this content.

**BrianHenson** · 03-15-2024, 08:27 AM

Thank you!!!

**AliGW** · 03-15-2024, 08:30 AM

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

**DJunqueira** · 03-15-2024, 09:43 AM

Tks for the feedback, glad to have helped.

**DJunqueira** · 03-18-2024, 12:39 PM

To add new values to the table go to the bottom right corner and press [TAB].
For the add on table you will need also to add a new column to be marked. Go to column 'M', add new column, select L6:L7 and drag them to the side, right click the new selection box in M7 and choose Format Control, then link the control to $M$7 cell.
In the formula now in O7 adjust the part TRANSPOSE($C$7:$M$7) to reflect the new option.

**BrianHenson** · 03-19-2024, 04:23 PM

Hello Junqueira. I have attached the spreadsheet once again. If you will notice on the commercial price tab, I added 900S to the mix ID table. However, the 900S does not appear in the dropdown list on the commercial selection tab. What am I doing wrong? Thank you

**DJunqueira** · 03-19-2024, 06:49 PM

You was right, it wasn't adding the new element, but now I hard coded it and it will.

Formula:

Please Login or Register  to view this content.

**wk9128** · 03-21-2024, 12:17 AM

BrianHenson You're Welcome. Glad to help . Thank You for the feedback and rep.

Need help totaling based on selections

LinkBack

Thread Tools

Rate This Thread

Display

Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Re: Need help totaling based on selections

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Totaling multiple colm based on date

[SOLVED] Noob Totaling rows based on Column heading

[SOLVED] Totaling Donors based on Honoree

[SOLVED] Help with totaling (inserting a template and totals seeing it and auto totaling the data)

Totaling based on search criteria

Totaling Revenue based on Source

[SOLVED] Totaling amounts based on duration

Bookmarks

Bookmarks

Posting Permissions