SUM except the strikethrough numbers? - NON_VBA

[_Landon]

Hello,

As this is my first post I apologize if this has been answered previously, I am new here and was unable to find any similar posts.

Let me catch you up on what is happening in the background as to why I need this function.

I am running Windows 11 operating through Office365, however my excel document is non-networked and does not need to be connected to any other sheets in order to work. I manually insert the documents I need each week and build a new week version based off the document sheets inserted. My struggle stems from an EXTREMELY tight administrator security system that continuously locks my documents and disables macros due to 365 requiring internet access and the possibility of malware being applied. I have no ability to override in properties as the option has been removed.

I was previously running the VBA script:

Cell F66: =SumNoStrikethrough(O7:O71)

Function SumNoStrikethrough(rng As Range)
Dim cel As Range
Dim s As Double
For Each cel In rng
If Not cel.Font.Strikethrough Then
s = s + Val(cel.Value)
End If
Next cel
SumNoStrikethrough = s
End Function

After running this VBA I would have to manually type ctrl+alt+f9 to get the cell to refresh the new SUM.
I need the SUM of numbers in column "O" to appear in cell F66, and when a number gets strikethrough I need it to automatically update cell F66 with the new SUM. Is there a Formula that can do this without needing access to VBA macros?

Thank you!

 

[Joe4]

Welcome to the Board!

Unfortunately, native Excel formula can only run off the values in the cells. They do not consider any formatting that may be applied to the cells.
So I don't know how do that without some code of some sort. Maybe instead of strikethrough, you could use some "helper columns/cells" to indicate which cells to ignore that you could incorporate into your formulas?

 

[JEC]

Nope

 

[_Landon]

Welcome to the Board!

Unfortunately, native Excel formula can only run off the values in the cells. They do not consider any formatting that may be applied to the cells.
So I don't know how do that without some code of some sort. Maybe instead of strikethrough, you could use some "helper columns/cells" to indicate which cells to ignore that you could incorporate into your formulas?

Unfortunately I need to use strikethrough to know personnel hours, approved vs. projected vs. cancelled. The strikethrough is the only was to convey that and still have the number within the spreadsheet without deleting.

 

[Joe4]

Well, there is no getting around Excel's functionality. So as I see it you have two options:

1. Petition your security system to allow VBA/Macros. Note that this does NOT have to be done for all Excel files. You can use Trusted Locations to limit it to just files found in a certain location on the directory, or digitally sign your VBA/Macros which then you can set your system to only allow macros with that specific signature.

2. Re-structure the format of your file so that you can do it in another way, like I suggested.
For example, you could have another column to indicate which ones have been cancelled (so you do not need to delete the hours), like this:

So cell O13 is just a simple SUM function, but the formula in O14, which excludes the "Cancelled" values looks like this:

=SUMIF(P6:P11,"<>C",O6:O11)

 

[Snakehips]

@_Landon Here is a possible way you might create a useful 'helper column' ???????

In Name Manager, create a named range 'Struckthro'
In 'Refers To:' enter

 =GET.CELL(23,Sheet2!$O7)

then close.

Then formula

=O7*NOT(Struckthro)

in P7 and drag down should give values if not struck thro.

Formula in F66 to sum P7:P71

If that is acceptable then you will just need to ensure that the P formulas recalculate.
Using the cell Format options to apply the strikethrough will probably not do it.
Using Paste Format from a struck throough cell should. F9 should.
**Edit. It would appear that the Strikethroughs are not showing in the XL2BB below so I have coloured those cells orange.

AutoTraining.xlsm
	F	G	H	I	J	K	L	M	N	O	P
6											Helper
7										1	1
8										2	2
9										3	0
10										4	4
11										5	0
12										6	0
13										7	7
14										8	8
15										9	9
16										10	10
17										11	11
18										12	12
19										13	13
20										14	14
21										15	15
22										16	0
23										17	17
24											0
25											0
26										23	0
27										24	24
28										25	25
29										26	26
30										27	27
31										28	28
32										29	29
33										30	30
34										31	31
35										32	32
36										33	33
37										34	34
38										35	35
39										36	36
40										37	37
41										38	38
42										39	39
43										40	40
44										41	41
45										42	42
46										43	43
47										44	44
48										45	45
49										46	46
50										47	47
51										48	48
52										49	49
53										50	50
54										51	51
55										52	52
56										53	0
57										54	0
58										55	0
59										56	0
60										57	0
61										58	0
62										59	59
63										60	0
64										61	0
65										62	0
66	1225									63	0
67										64	0
68										65	0
69										66	0
70										67	67
71										68	68
Sheet2

Cell Formulas
Range	Formula
F66	F66	=SUM(P7:P57)
P7:P71	P7	=O7*NOT(Struckthro)

Fingers crossed I'm not talking rubbish!

 

[_Landon]

Well, there is no getting around Excel's functionality. So as I see it you have two options:

1. Petition your security system to allow VBA/Macros. Note that this does NOT have to be done for all Excel files. You can use Trusted Locations to limit it to just files found in a certain location on the directory, or digitally sign your VBA/Macros which then you can set your system to only allow macros with that specific signature.

2. Re-structure the format of your file so that you can do it in another way, like I suggested.
For example, you could have another column to indicate which ones have been cancelled (so you do not need to delete the hours), like this:

View attachment 113006

So cell O13 is just a simple SUM function, but the formula in O14, which excludes the "Cancelled" values looks like this:

=SUMIF(P6:P11,"<>C",O6:O11)

Thank you, this may be the route I have to take. I will pursue option one like you suggested with regards to allowing it via a digital certificate signature. If that is denied I will most likely add a helper column and run that formula. Thank you for your help!

 

[Joe4]

You are welcome!
Good luck! I hope your Security Department allows the Digital Signature option. That would prevent malicious code, because that code would not be signed.

 

[_Landon]

@_Landon Here is a possible way you might create a useful 'helper column' ???????

In Name Manager, create a named range 'Struckthro'
In 'Refers To:' enter

 =GET.CELL(23,Sheet2!$O7)

then close.

Then formula

=O7*NOT(Struckthro)

in P7 and drag down should give values if not struck thro.

Formula in F66 to sum P7:P71

If that is acceptable then you will just need to ensure that the P formulas recalculate.
Using the cell Format options to apply the strikethrough will probably not do it.
Using Paste Format from a struck throough cell should. F9 should.
**Edit. It would appear that the Strikethroughs are not showing in the XL2BB below so I have coloured those cells orange.

AutoTraining.xlsm
	F	G	H	I	J	K	L	M	N	O	P
6											Helper
7										1	1
8										2	2
9										3	0
10										4	4
11										5	0
12										6	0
13										7	7
14										8	8
15										9	9
16										10	10
17										11	11
18										12	12
19										13	13
20										14	14
21										15	15
22										16	0
23										17	17
24											0
25											0
26										23	0
27										24	24
28										25	25
29										26	26
30										27	27
31										28	28
32										29	29
33										30	30
34										31	31
35										32	32
36										33	33
37										34	34
38										35	35
39										36	36
40										37	37
41										38	38
42										39	39
43										40	40
44										41	41
45										42	42
46										43	43
47										44	44
48										45	45
49										46	46
50										47	47
51										48	48
52										49	49
53										50	50
54										51	51
55										52	52
56										53	0
57										54	0
58										55	0
59										56	0
60										57	0
61										58	0
62										59	59
63										60	0
64										61	0
65										62	0
66	1225									63	0
67										64	0
68										65	0
69										66	0
70										67	67
71										68	68
Sheet2

Cell Formulas
Range	Formula
F66	F66	=SUM(P7:P57)
P7:P71	P7	=O7*NOT(Struckthro)

Fingers crossed I'm not talking rubbish!

Thank you for another option, this is very similar to what Joe4 has suggested also. I believe his method may be much simpler for my needs in terms of visible numbers on the sheet