Replace Subtotal with Sum

[cagauravgarg]

Is there any shortcut way to replace the Subtotal formula in groupings with Sum formula? I have multiple level of groupings in the excel. Attached an image for example

 

[Georgiboy]

Perhaps you could use CTRL+F and, select the Replace tab and put:
Find what: =SUBTOTAL(9,
Replace with: =SUM(
Change the look in box to: Formulas

Click Replace All

 

[James006]

Hi,
Usually, the challenge is exactly the opposite !!! Converting SUMs to SUBTOTALs
What is your final objective ???

 

[cagauravgarg]

No Find and replace won't work. Say there are 11 rows and A1 to A11 and have 2 groupings each 5 times and then I have total of all 5 grouping in 11th row. By doing replace the formula in 11th row will be like =Sum(A1:A10) which is wrong as it will add individual sum of all the 5 groupings in between. However subtotal ignores subtotal applied within in its range.

 

[Ajeet]

Is there any shortcut way to replace the Subtotal formula in groupings with Sum formula? I have multiple level of groupings in the excel. Attached an image for example

View attachment 87738

Try this
Press ctrl + ~
Then go to replace and type
=SUBTOTAL(9,
in find what field and
=sum(
in "Replace with" field and press "Replace All" button
Press again ctrl + ~
Hope this might help!!

 

[cagauravgarg]

@Ajeet

I'm aware of the shortcuts that you have mentioned but it doesn't work. Here is a sample of the excel range. I have applied the subtotal formula in rows 11, 19 and 20. Now if I use find and replace and convert those formulas to sum, rows 11 and 19 will not have any impact, but the total value in Row 20 will be impacted. This is just a sample range I have multiple levels of groupings having 5k+ rows. I can't do this manually.

Book1
	A	B	C	D	E	F	G
1
2				Location IS -
3
4		Acct #	Unique ID	$'000	Jan-20	Feb-20
5				Regular
6		501.1	501.1 - Retail tires	Retail tires	1	2
7		501.10	501.10 - Labor	Labor	1	2
8		501.7	501.7 - Commercial tire	Commercial tire	1	2
9		501.4	501.4 - Chains	Chains	1	2
10		501	501 - Sales	Sales	342	32
11				Regular	346	40
12				Miscellaneous
13		501.90	501.90 - Finance charge	Finance charge	1	2
14		501.9.5	501.9.5 - Oregon state surcharge	Oregon state surcharge	1	2
15		501.9.4	501.9.4 - Merchant services fraud	Merchant services fraud	1	2
16		501.9.3	501.9.3 - Nat. account charge back	Nat. account charge back	1	2
17		501.9.2	501.9.2 - Pinmarx	Pinmarx	1	2
18		501.9	501.9 - Misc sales	Misc sales	12	31
19				Miscellaneous	17	41
20				Net sales	363	81
21
Sheet3

Cell Formulas
Range	Formula
D2	D2	="Location IS - "&D1
F4	F4	=EOMONTH(E4,1)
C6:C10,C13:C18	C6	=B6&" - "&D6
D11	D11	=D5
E11:F11	E11	=SUBTOTAL(9,E6:E10)
D19	D19	=D12
E19:F19	E19	=SUBTOTAL(9,E12:E18)
E20:F20	E20	=SUBTOTAL(9,E5:E19)

 

[cagauravgarg]

@Georgiboy @James006 - Do you have anything for this?

 

[James006]

You did not answer the question in post # 3 ...

For sure, you already know that for your Grand Total, Subtotal will only sum all the subtotals located above ...

 

[cagauravgarg]

If I had applied subtotal it ignores all the subtotal applied above. But the Sum will do the opposite so I just can't use find and replace over this. Actually, this is just to ensure my databook is consistent. All my other tabs are prepared using Sum so I can't use subtotal in this file. My objective is meet the client requirements that's all

 

[James006]

Hi again,

Despite the fact I do not understand your situation ...
You can test following

Sub ChangeCellFormula()
Dim rng As Range, rngform As String
    Set rng = Range("E11")
    rngform = rng.Formula
    rngform = Replace(rngform, "SUBTOTAL(9,", "SUM(")
    rng.Formula = rngform
End Sub