requesting CREATIVE help for this need...

Hoffman

Board Regular
Joined
Oct 19, 2007
Messages
222
Office Version
  1. 365
Platform
  1. MacOS
Sat 8-26-23

Hi EXCELent helpers,

I have this sort of unique need and have no idea where to begin. I'll briefly tell you what it's for and what I'm trying to achieve.
Thanks in advance for reading.

In Excel I have about 20 parts of my bike listed in Column A and in column B their weights in grams.
At the bottom of column B I have the total weight for all parts like normal.

However, I'd like to "turn off" or unclick some of these bike part entires in the list at times so I can see what the bike weighs without some things. So sometimes I want to see the wight at the bottom with all 20, other times, just 14 of them, sometimes 7 of them, etc

So for example
A B (grams)
bike light 40
wheels 1580

total grams = 1620

but I'd like to be able to see the total when I do NOT want the bike light weight or even able to ignore or turn off many items in the list.

Any creative ideas how to begin to do this would be appreciated!!!

Barry
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Would this be any use?
Add/Remove the "x" values as required.

23 08 26.xlsm
ABC
1PartWeightExclude these
2Part 15
3Part 26x
4Part 38x
5Part 46x
6Part 59
7Part 65x
8Part 76x
9Part 82
10Part 93x
11Total5016
Parts
Cell Formulas
RangeFormula
B11B11=SUM(B2:B10)
C11C11=SUMIF(C2:C10,"<>x",B2:B10)
 
Upvote 0
However, I'd like to "turn off" or unclick some of these bike part entires in the list at times so I can see what the bike weighs without some things. So sometimes I want to see the wight at the bottom with all 20, other times, just 14 of them, sometimes 7 of them, etc
Use Subtotal with Function 109

It removes filtered items from the total

=Subtotal(109,Column to be totaled)
 
Upvote 0
Would this be any use?
Add/Remove the "x" values as required.

23 08 26.xlsm
ABC
1PartWeightExclude these
2Part 15
3Part 26x
4Part 38x
5Part 46x
6Part 59
7Part 65x
8Part 76x
9Part 82
10Part 93x
11Total5016
Parts
Cell Formulas
RangeFormula
B11B11=SUM(B2:B10)
C11C11=SUMIF(C2:C10,"<>x",B2:B10)

yes Peter- I imagined something like this- gonna give it a try and report back.
Thanks
Barry
 
Upvote 0
Would this be any use?
Add/Remove the "x" values as required.

23 08 26.xlsm
ABC
1PartWeightExclude these
2Part 15
3Part 26x
4Part 38x
5Part 46x
6Part 59
7Part 65x
8Part 76x
9Part 82
10Part 93x
11Total5016
Parts
Cell Formulas
RangeFormula
B11B11=SUM(B2:B10)
C11C11=SUMIF(C2:C10,"<>x",B2:B10)

oops sorry- I forgot to mention next to each item is 3 columns not just 1. the columns are grams, pounds, kilograms.
Does this affect what you created?
so looks like

items grams lbs kg
frame 6486 14.3 6.4

Barry
 
Upvote 0
so looks like

items grams lbs kg
frame 6486 14.3 6.4
Unfortunately that doesn't format well. I understand what you are saying this time but please investigate XL2BB for providing sample data in a good format & easy to copy to our sheets for testing.

I forgot to mention next to each item is 3 columns not just 1. the columns are grams, pounds, kilograms.
Does this affect what you created?
Not at all, just move my "Exclude" column further to the right & adjust the ranges in the formula.

23 08 26.xlsm
ABCDE
1PartWeightExclude these
2Part 15
3Part 26x
4Part 38x
5Part 46x
6Part 59
7Part 65x
8Part 76x
9Part 82
10Part 93x
11Total5016
Parts
Cell Formulas
RangeFormula
B11B11=SUM(B2:B10)
E11E11=SUMIF(E2:E10,"<>x",B2:B10)
 
Upvote 0
Solution
Unfortunately that doesn't format well. I understand what you are saying this time but please investigate XL2BB for providing sample data in a good format & easy to copy to our sheets for testing.


Not at all, just move my "Exclude" column further to the right & adjust the ranges in the formula.

23 08 26.xlsm
ABCDE
1PartWeightExclude these
2Part 15
3Part 26x
4Part 38x
5Part 46x
6Part 59
7Part 65x
8Part 76x
9Part 82
10Part 93x
11Total5016
Parts
Cell Formulas
RangeFormula
B11B11=SUM(B2:B10)
E11E11=SUMIF(E2:E10,"<>x",B2:B10)


WORKED PERFECTLY

just some formatting things to improve but the concept is solved.

Thanks so much-
you are awesome.

Barry
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,329
Members
452,635
Latest member
laura12345

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