Formula help with removing duplicates while looking at other column criteria

[samide2001]

All of the formulas I am doing will be on a summary tab that looks at data added on monthly tabs "Jan", "Feb", etc. . So I cannot add helper columns. The number of rows per column will change, and the number of duplicates will also change. So, if the formula could be derived to look at the entire column that would be great! I may not be the only end-user of the spreadsheet. The end-user will be downloading the data, copy/pasting into the monthly tabs, and then the formulas should hopefully auto-populate the monthly summary for them.

On the summary tab, I need the formula to look at the data on the associated tab column A and for each unique ID number I need to look at column Y and subtract the amount in column W if column B has "canceled" AND column AA is blank.

So for the data set below, there are 5 "canceled" instances in Column B, but only 3 of them have a blank cell in Column AA, and of those 3 there are only 2 unique IDs in Column A. So the result would be $28.27

2023 - TikTokShop Spreadsheet.xlsx
	A	B																					W	X	Y	Z	AA
1	Order ID	Order Status																					Taxes	Order Amount	Order Refund Amount	Created Time	Paid Time
2	Platform unique order ID.	Current order status.																						Order total amount paid by the buyer.	Order total refund amount of all returned SKUs.	Order created time.	Order paid time.
3	576473553911582988	Completed																					1.52	12.02		07/31/2023 6:14:07 PM	07/31/2023 6:14:10 PM
4	576473470461448948	Completed																					1.22	24.22		07/31/2023 10:38:19 AM	07/31/2023 10:38:22 AM
5	576473470461448948	Completed																					1.22	24.22		07/31/2023 10:38:19 AM	07/31/2023 10:38:22 AM
6	576473441536020829	Completed																					0.5	14.49		07/31/2023 8:11:24 AM	07/31/2023 8:11:27 AM
7	576473423026557177	Completed																					1.09	16.09		07/31/2023 6:35:14 AM	07/31/2023 6:35:17 AM
8	576473308230422775	Completed																					1.05	16.05		07/30/2023 6:26:15 PM	07/30/2023 6:26:48 PM
9	576473303896003495	Completed																					0.6	15.6		07/30/2023 5:45:11 PM	07/30/2023 5:45:35 PM
10	576473291302408951	Completed																					1.09	11.59		07/30/2023 4:47:56 PM	07/30/2023 4:47:59 PM
11	576473221986357846	Completed																					1.16	17.15		07/30/2023 10:15:21 AM	07/30/2023 10:15:24 AM
12	576473223449055830	Completed																					1.38	20.37		07/30/2023 10:13:48 AM	07/30/2023 10:13:51 AM
13	576473222655545942	Completed																					1.52	22.51		07/30/2023 10:06:41 AM	07/30/2023 10:06:44 AM
14	576473201202336383	Canceled																					1.47	22.46	16.05	07/30/2023 8:23:02 AM
15	576473198163628143	Canceled																					1.68	26.67	16.01	07/30/2023 8:18:43 AM	07/30/2023 8:19:38 AM
16	576473198163628143	Canceled																					1.68	26.67	4.27	07/30/2023 8:18:43 AM	07/30/2023 8:19:38 AM
107	576472282577736405	Completed																					1.43	11.93		07/26/2023 6:16:51 PM	07/26/2023 6:16:54 PM
108	576472272126316567	Canceled																					2.62	32.62	16.31	07/26/2023 5:36:22 PM
109	576472272126316567	Canceled																					2.62	32.62	16.31	07/26/2023 5:36:22 PM
110	576472273471377665	Completed																					1.81	14.06		07/26/2023 5:31:15 PM	07/26/2023 5:34:48 PM
Jul

 

[SanjayGMusafir]

So I cannot add helper columns

Helper columns are not required but you can create a new sheet that consolidates all the data from 12 sheets after that life would be much easy for you. The more complicated formula you create or use chances are to make some mistake. Or Just in case you need a slight modification it shall become a headache.

So My suggestion is to create a consolidated data sheet and then make a summary.

 

[samide2001]

Helper columns are not required but you can create a new sheet that consolidates all the data from 12 sheets after that life would be much easy for you. The more complicated formula you create or use chances are to make some mistake. Or Just in case you need a slight modification it shall become a headache.

So My suggestion is to create a consolidated data sheet and then make a summary.

This isn't the only formula on the summary sheet, so if I can get by without the helper consolidated sheet that would be ideal. I don't want numerous helper tab sheets running because then I run the risk of the other end-users messing with those sheets. Or even messing with the one helper consolidated sheet. The less people can mess with the end file the better.

But if that is the only way, then that is what I will do. But I am sure there is a formula that can work.

 

[samide2001]

All of the formulas I am doing will be on a summary tab that looks at data added on monthly tabs "Jan", "Feb", etc. . So I cannot add helper columns. The number of rows per column will change, and the number of duplicates will also change. So, if the formula could be derived to look at the entire column that would be great! I may not be the only end-user of the spreadsheet. The end-user will be downloading the data, copy/pasting into the monthly tabs, and then the formulas should hopefully auto-populate the monthly summary for them.

On the summary tab, I need the formula to look at the data on the associated tab column A and for each unique ID number I need to look at column Y and subtract the amount in column W if column B has "canceled" AND column AA is blank.

So for the data set below, there are 5 "canceled" instances in Column B, but only 3 of them have a blank cell in Column AA, and of those 3 there are only 2 unique IDs in Column A. So the result would be $28.27

2023 - TikTokShop Spreadsheet.xlsx
	A	B																					W	X	Y	Z	AA
1	Order ID	Order Status																					Taxes	Order Amount	Order Refund Amount	Created Time	Paid Time
2	Platform unique order ID.	Current order status.																						Order total amount paid by the buyer.	Order total refund amount of all returned SKUs.	Order created time.	Order paid time.
3	576473553911582988	Completed																					1.52	12.02		07/31/2023 6:14:07 PM	07/31/2023 6:14:10 PM
4	576473470461448948	Completed																					1.22	24.22		07/31/2023 10:38:19 AM	07/31/2023 10:38:22 AM
5	576473470461448948	Completed																					1.22	24.22		07/31/2023 10:38:19 AM	07/31/2023 10:38:22 AM
6	576473441536020829	Completed																					0.5	14.49		07/31/2023 8:11:24 AM	07/31/2023 8:11:27 AM
7	576473423026557177	Completed																					1.09	16.09		07/31/2023 6:35:14 AM	07/31/2023 6:35:17 AM
8	576473308230422775	Completed																					1.05	16.05		07/30/2023 6:26:15 PM	07/30/2023 6:26:48 PM
9	576473303896003495	Completed																					0.6	15.6		07/30/2023 5:45:11 PM	07/30/2023 5:45:35 PM
10	576473291302408951	Completed																					1.09	11.59		07/30/2023 4:47:56 PM	07/30/2023 4:47:59 PM
11	576473221986357846	Completed																					1.16	17.15		07/30/2023 10:15:21 AM	07/30/2023 10:15:24 AM
12	576473223449055830	Completed																					1.38	20.37		07/30/2023 10:13:48 AM	07/30/2023 10:13:51 AM
13	576473222655545942	Completed																					1.52	22.51		07/30/2023 10:06:41 AM	07/30/2023 10:06:44 AM
14	576473201202336383	Canceled																					1.47	22.46	16.05	07/30/2023 8:23:02 AM
15	576473198163628143	Canceled																					1.68	26.67	16.01	07/30/2023 8:18:43 AM	07/30/2023 8:19:38 AM
16	576473198163628143	Canceled																					1.68	26.67	4.27	07/30/2023 8:18:43 AM	07/30/2023 8:19:38 AM
107	576472282577736405	Completed																					1.43	11.93		07/26/2023 6:16:51 PM	07/26/2023 6:16:54 PM
108	576472272126316567	Canceled																					2.62	32.62	16.31	07/26/2023 5:36:22 PM
109	576472272126316567	Canceled																					2.62	32.62	16.31	07/26/2023 5:36:22 PM
110	576472273471377665	Completed																					1.81	14.06		07/26/2023 5:31:15 PM	07/26/2023 5:34:48 PM
Jul

Actually after looking at the data further... The formula could be simplified.
I still need to remove the duplicate IDs in column A, check column B for "canceled", and only count blank cells in column AA, but only need to sum the results in column X.
Or if it would be even simpler, we could probably only look at column AA for blank cells since the order was never paid for; therefore, it would automatically be canceled.

 

[samide2001]

Ok. Looking at it some more... I think it can be simplified.

What I think I need is something like this:

=LET(u,UNIQUE(FILTER($A$3:$A$1000,$A3:$A$1000<>"")),SUM(SUMIFS($D:$D,$A:$A,u,$B:$B,"<>canceled",$G:$G,"<>")/COUNTIFS($A:$A,u)))

But with the data set below the result isn't bringing up the correct answer.

The data below is already filtered to only show canceled orders in column B.

So the formula still needs to filter to only look at unique IDs in Column A. Then it needs to look at column B for "canceled" status and column G for a paid time (ignoring blank cells). Then from that result it needs sum column D and subtract column C.

2023 - TikTokShop Spreadsheet.xlsx
	A	B	C	D	E	F	G
1	Order ID	Order Status	Taxes	Order Amount	Order Refund Amount	Created Time	Paid Time
13	576473201202336383	Canceled	1.47	22.46	16.05	07/30/2023 8:23:02 AM
14	576473198163628143	Canceled	1.68	26.67	16.01	07/30/2023 8:18:43 AM	07/30/2023 8:19:38 AM
15	576473198163628143	Canceled	1.68	26.67	4.27	07/30/2023 8:18:43 AM	07/30/2023 8:19:38 AM
25	576472910325125299	Canceled	0.64	14.13	8.14	07/29/2023 6:29:36 AM	07/29/2023 6:29:39 AM
32	576472737783583728	Canceled	1.24	16.24	16.24	07/28/2023 1:08:22 PM	07/28/2023 1:08:25 PM
49	576472551669732200	Canceled	0.94	14.43	8.03	07/27/2023 7:16:38 PM	07/27/2023 7:17:06 PM
59	576472505876648245	Canceled	1.29	14.78	8.22	07/27/2023 3:32:28 PM	07/27/2023 3:32:30 PM
63	576472471512585154	Canceled	1.34	16.34	8.17	07/27/2023 12:18:33 PM	07/27/2023 12:18:37 PM
64	576472471512585154	Canceled	1.34	16.34	8.17	07/27/2023 12:18:33 PM	07/27/2023 12:18:37 PM
73	576472446468198625	Canceled	0.9	12.9	6.45	07/27/2023 10:30:27 AM	07/27/2023 10:31:19 AM
74	576472446468198625	Canceled	0.9	12.9	6.45	07/27/2023 10:30:27 AM	07/27/2023 10:31:19 AM
107	576472272126316567	Canceled	2.62	32.62	16.31	07/26/2023 5:36:22 PM
108	576472272126316567	Canceled	2.62	32.62	16.31	07/26/2023 5:36:22 PM
118	576472173537759938	Canceled	0.6	13.59	7.6	07/26/2023 8:23:05 AM
120	576471947960291952	Canceled	1.15	15.14	8.66	07/25/2023 10:20:55 AM
132	576471692102963229	Canceled	1.17	11.67	11.67	07/24/2023 8:57:47 AM
134	576471581935833731	Canceled	0.6	21.59	15.6	07/23/2023 9:48:13 PM
138	576471499699032822	Canceled	1.12	14.11	7.71	07/23/2023 1:09:33 PM	07/23/2023 1:09:37 PM
139	576471499572613674	Canceled	0.68	7.68	7.68	07/23/2023 1:04:46 PM
145	576471351433466840	Canceled	0.6	21.59	15.6	07/22/2023 10:00:44 PM
151	576471266098843704	Canceled	1.8	22.8	11.4	07/22/2023 1:26:16 PM	07/22/2023 1:28:55 PM
152	576471266098843704	Canceled	1.8	22.8	11.4	07/22/2023 1:26:16 PM	07/22/2023 1:28:55 PM
161	576471122029285411	Canceled	1.24	11.74	11.74	07/21/2023 10:34:12 PM
171	576470969234723697	Canceled	2.66	23.66	23.66	07/21/2023 7:23:57 AM
172	576470926672826818	Canceled	1.2	11.7	11.7	07/21/2023 5:50:24 AM	07/21/2023 5:50:28 AM
192	576470787577778407	Canceled	1.4	11.9	11.9	07/20/2023 12:18:09 PM	07/20/2023 12:19:42 PM
208	576470596244640010	Canceled	3.24	41.74	15.18	07/19/2023 4:59:32 PM
209	576470596244640010	Canceled	3.24	41.74	11.38	07/19/2023 4:59:32 PM
210	576470596244640010	Canceled	3.24	41.74	15.18	07/19/2023 4:59:32 PM
215	576470565671768656	Canceled	1.54	12.04	12.04	07/19/2023 1:33:24 PM
240	576470519023375309	Canceled	2.64	22.64	22.64	07/19/2023 9:13:49 AM
249	576470403564999594	Canceled	1.9	15.9	15.9	07/18/2023 7:41:16 PM	07/18/2023 7:41:20 PM
281	576469747735892351	Canceled	2.52	23.52	11.76	07/14/2023 9:25:09 AM	07/14/2023 9:25:44 AM
282	576469747735892351	Canceled	2.52	23.52	11.76	07/14/2023 9:25:09 AM	07/14/2023 9:25:44 AM
286	576469747448188941	Canceled	0.6	11.1	11.1	07/14/2023 9:06:47 AM
288	576469747417125610	Canceled	1.33	22.32	15.95	07/14/2023 9:03:34 AM
289	576469744451490569	Canceled	1.8	27.3	13.65	07/14/2023 7:58:30 AM
290	576469744451490569	Canceled	1.8	27.3	13.65	07/14/2023 7:58:30 AM
312	576469733401334269	Canceled	0.93	18.32	9.33	07/14/2023 1:11:01 AM
315	576469723151962577	Canceled	1.05	11.55	11.55	07/13/2023 6:53:42 PM	07/13/2023 6:54:23 PM
317	576469710786171033	Canceled	1.16	16.16	16.16	07/13/2023 1:35:30 PM
321	576469698812678645	Canceled	1.17	11.67	11.67	07/13/2023 8:46:10 AM	07/13/2023 8:46:13 AM
323	576469691430375745	Canceled	1.36	25.36	25.36	07/13/2023 5:00:42 AM
354	576469658358944424	Canceled	3.02	24.02	12.01	07/12/2023 9:23:05 AM	07/12/2023 9:23:31 AM
355	576469658358944424	Canceled	3.02	24.02	12.01	07/12/2023 9:23:05 AM	07/12/2023 9:23:31 AM
Sheet1

 

[samide2001]

I have tried this to no avail, as well.

=LET(u,UNIQUE(FILTER($A$3:$A$1000,$A3:$A$1000<>"")),SUM(SUMIFS($D:$D,$A:$A,u,$B:$B,"<>canceled",$G:$G,"<>")-SUMIFS($C:$C,$A:$A,u,$B:$B,"<>canceled",$G:$G,"<>")))