Trying to assign subtotal to a specific cell by keyword

[lumbernuts]

Hi, I currently have to manually put the subtotal in the proper category, how would i do the following so if for example it sees "FREEDOM MOBILE" in the description it would automatically assign it to "Cell Phone" column. Thanks.

Bank Bank Bank GST GST Source deduction Corporate Corporate Vehicle Ford Credit Heavy Heavy Equip Insurance for Heavy Equip Tool Cell Office Lic, Dues Work Description Withdrawals Deposits Balance Revenue Collected ITC's arrears/penalty GST Payment Subtotal Calculation Income Tax Payment(provincial) Income Tax Payment(federal) Expenses Payment Equip Hauling Heavy Equip Maintainence Fuel Supplies Tools Phone Service Expenses Mem. Clothes Cell Phone Balance Forward $ - $ - $ - $ - FREEDOM MOBILE $ (61.90) $ - $ - $ 2.95 $ (58.95) 58.95 $ 58.95

 

[lumbernuts]

*there's only supposed to be one "cell phone" category

 

[fjns]

Hi, try this formula in the cells W3 and AB3
=IF(A3="FREEDOM MOBILE",J3,"")

CellPhone.xlsx

 

[lumbernuts]

Hi, thx for the reply, so thats what ive been trying but I just get a blank cell when i plug that in. I should clarify that in J4 I have the formula =B3+G3, not sure if that matters. Some of these columns will have a handful of keywords to look for like for example in the "Tools" category , itll look for 'Home Depot' "Lowes" etc. etc. Not sure if I should set up an index maybe?

 

[fjns]

Hi, thx for the reply, so thats what ive been trying but I just get a blank cell when i plug that in. I should clarify that in J4 I have the formula =B3+G3, not sure if that matters. Some of these columns will have a handful of keywords to look for like for example in the "Tools" category , itll look for 'Home Depot' "Lowes" etc. etc. Not sure if I should set up an index maybe?

Hi, it would be nice to see the formulas and more rows of your table.

 

[lumbernuts]

Silvertip Master Sheet 2022.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
24				Bank	Bank	Bank		GST	GST	Source deduction			Corporate	Corporate	Vehicle	Ford Credit	Heavy	Heavy Equip	Insurance for	Heavy Equip				Tool	Tool	Crane	Office	Lic, Dues	Work		Disability				Misc.	Source			Prof.	Shareholder	Charity
25	Date	Code	Description	Withdrawals	Deposits	Balance	Revenue	Collected	ITC's	arrears/penalty	GST Payment	Subtotal Calculation	Income Tax Payment(provincial)	Income Tax Payment(federal)	Expenses	Payment	Equip	Hauling	Heavy Equip	Maintainence	Fuel	Supplies	Tools	Rentals	Maintainence	Service	Expenses	Mem.	Clothes	Cell Phone	Insurance	WCB	Meals	Wages	Travel	deductions	Money mart	Advertising	Fees	Draw	Donations	Personal tax payment	Control
26			Balance Forward	$ -		$8,389.63	$ -	$ -	$ -																																		$ 8,389.63
27	04-Jan-22	CW	FREEDOM MOBILE	$ (61.90)		$8,327.73	$ -	$ -	$ 2.95			$ (58.95)																															$ 8,209.83
28	04-Jan-22	PR	REAL CDN. SUPER	$ (174.12)		$8,153.61	$ -	$ -	$ 8.29			$ (165.83)																															$ 7,821.95
29	04-Jan-22	OP	RECURRING PYMNT 1JAN2022AMAZON.CA ON	$ (239.60)		$7,914.01	$ -	$ -	$ 11.41			$ (228.19)																															$ 7,457.63
30	04-Jan-22	CW	INTERAC ETRNSFR SENT QB 20220040032F824C4	$ (140.00)		$7,774.01	$ -	$ -	$ 6.67			$ (133.33)																															$ 7,507.34
PDFTables.com

Cell Formulas
Range	Formula
G26:G30	G26	=E26/(-1.05)
H26:H30	H26	=E26+G26
I26:I30	I26	=D26*(-0.047619047619)
AD27	AD27	=IF(C3="FREEDOM MOBILE",L3,"")
L27:L30	L27	=D27+I27
AQ26:AQ30	AQ26	=SUM(D26:AP26)

 

[fjns]

Hi, I modified your table a bit.

SilvertipMasterSheets2022.xlsx

 

[lumbernuts]

Hi, thx alot! Funny thing is ... this works in google sheets but in excel i still get this error message. Using excel 2013. Anyways I can just move it all over to google sheets if needed. Thx again!

 

[lumbernuts]

Maybe this is why , Excel 2013 wants this formula in a different format??

 

[lumbernuts]

Sheets has it like this