requesting CREATIVE help for this need...

[Hoffman]

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

 

[Peter_SSs]

Would this be any use?
Add/Remove the "x" values as required.

23 08 26.xlsm
	A	B	C
1	Part	Weight	Exclude these
2	Part 1	5
3	Part 2	6	x
4	Part 3	8	x
5	Part 4	6	x
6	Part 5	9
7	Part 6	5	x
8	Part 7	6	x
9	Part 8	2
10	Part 9	3	x
11	Total	50	16
Parts

Cell Formulas
Range	Formula
B11	B11	=SUM(B2:B10)
C11	C11	=SUMIF(C2:C10,"<>x",B2:B10)

 

[SanjayGMusafir]

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)

 

[Hoffman]

Would this be any use?
Add/Remove the "x" values as required.

23 08 26.xlsm
	A	B	C
1	Part	Weight	Exclude these
2	Part 1	5
3	Part 2	6	x
4	Part 3	8	x
5	Part 4	6	x
6	Part 5	9
7	Part 6	5	x
8	Part 7	6	x
9	Part 8	2
10	Part 9	3	x
11	Total	50	16
Parts

Cell Formulas
Range	Formula
B11	B11	=SUM(B2:B10)
C11	C11	=SUMIF(C2:C10,"<>x",B2:B10)

yes Peter- I imagined something like this- gonna give it a try and report back.
Thanks
Barry

 

[Hoffman]

Would this be any use?
Add/Remove the "x" values as required.

23 08 26.xlsm
	A	B	C
1	Part	Weight	Exclude these
2	Part 1	5
3	Part 2	6	x
4	Part 3	8	x
5	Part 4	6	x
6	Part 5	9
7	Part 6	5	x
8	Part 7	6	x
9	Part 8	2
10	Part 9	3	x
11	Total	50	16
Parts

Cell Formulas
Range	Formula
B11	B11	=SUM(B2:B10)
C11	C11	=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

 

[Peter_SSs]

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
	A	B	C	D	E
1	Part	Weight			Exclude these
2	Part 1	5
3	Part 2	6			x
4	Part 3	8			x
5	Part 4	6			x
6	Part 5	9
7	Part 6	5			x
8	Part 7	6			x
9	Part 8	2
10	Part 9	3			x
11	Total	50			16
Parts

Cell Formulas
Range	Formula
B11	B11	=SUM(B2:B10)
E11	E11	=SUMIF(E2:E10,"<>x",B2:B10)

 

[Hoffman]

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
	A	B	C	D	E
1	Part	Weight			Exclude these
2	Part 1	5
3	Part 2	6			x
4	Part 3	8			x
5	Part 4	6			x
6	Part 5	9
7	Part 6	5			x
8	Part 7	6			x
9	Part 8	2
10	Part 9	3			x
11	Total	50			16
Parts

Cell Formulas
Range	Formula
B11	B11	=SUM(B2:B10)
E11	E11	=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

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.