Sum column based on unique values in another column

[samide2001]

All of the formulas I am doing will be on a summary tab that looks at data added on another tab "Jan". So I cannot add helper columns. The number of rows per column will change each month, 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!

On the summary tab, I need the formula to look at the data in column W and sum the values for the unique numbers in column A. I also need the formula to subtract out the values from column W if column B has "canceled" as the status (also removing any duplicates). So, for the example below the correct value returned should be $7.65 for column W based on the uniqe values of column A - canceled orders in Column B.

2024 - TikTokShop Sales and Fees - Quick Numbers.xlsx
	A	B	C																				W
1	Order ID	Order Status	Order Substatus																				Taxes
2	Platform unique order ID.	Current order status.
3	576577737861468321	Shipped	In Transit																				1.09
4	576575207724716077	Shipped	In Transit																				5.26
5	576575207724716077	Shipped	In Transit																				5.26
6	576575207724716077	Shipped	In Transit																				5.26
7	576575207724716077	Shipped	In Transit																				5.26
8	576573793136054581	Shipped	Delivered																				0.7
9	576568733552841200	Shipped	Delivered																				0.6
10	576568733126005232	Canceled	Canceled																				0.6
Jan

Here is the summary tab: The formula I need will go in cell B6

2024 - TikTokShop Sales and Fees - Quick Numbers.xlsx
	A	B	C
3	Revenue
4	Sales	$ 93.00	$ -
5	Shipping	$ 9.31	$ -
6	Sales Tax	$ 7.65	$ -
7	Canceled Orders	$ 10.00	$ -
8	Shop Revenue	$ 84.66	$ -
9
10	Expenses
11	Selling Expenses
12	Referral Fees	$ 4.19	$ -
13	Affiliate Commisions	$ 3.00	$ -
14	Shipping Fee	$ 16.98	$ -
15	Total Expenses	$ 24.17	$ -
16
17	Net Profit	$ 60.49	$ -
Monthly Summary

Cell Formulas
Range	Formula
B4	B4	=SUM(Jan!$M:$M)
B5	B5	=SUMIF(Jan!$B:$B, "<>"&"Canceled", Jan!$Q:$Q)
C4	C4	=SUM(Feb!$M:$M)
C5	C5	=SUMIF(Feb!$B:$B, "<>"&"Canceled", Feb!$Q:$Q)
C6	C6	=SUM(Feb!$W:$W)
B7	B7	=SUMIF(Jan!$B:$B, "Canceled", Jan!$Y:$Y)-SUMIF(Jan!$B:$B, "Canceled", Jan!$W:$W)
C7	C7	=SUMIF(Feb!$B:$B, "Canceled", Feb!$Y:$Y)-SUMIF(Feb!$B:$B, "Canceled", Feb!$W:$W)
B8	B8	=SUM(B4+B5-B6-B7)
C8	C8	=SUM(C4+C5-C6-C5)
B12	B12	=-SUM('Jan2'!$Y:$Y)
C12	C12	=-SUM('Feb2'!$Y:$Y)
B13	B13	=-SUM('Jan2'!$AD:$AD)
C13	C13	=-SUM('Feb2'!$AD:$AD)
B14	B14	=-SUM('Jan2'!$Z:$Z)
C14	C14	=-SUM('Feb2'!$Z:$Z)
B15:C15	B15	=SUM(B12:B14)
B17:C17	B17	=B8-B15

I have tried a sumif formula with unique as the criteria, but got a spill error. When I moved it out this is the data I got (this was the first part before trying to figure out how to subtract column B):

2024 - TikTokShop Sales and Fees - Quick Numbers.xlsx
	D
27	1.09
28	$ 21.04
29	$ 0.70
30	$ 0.60
31	$ 0.60
32	$ -
Monthly Summary

Cell Formulas
Range	Formula
D27:D32	D27	=SUMIF(Jan!A:A,UNIQUE(Jan!$A3:A99999,FALSE,FALSE),Jan!W:W)
Dynamic array formulas.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[samide2001]

Glad we could help & thanks for the feedback.

Thanks so much for the help! If you could help with this query I would greatly appreciate it!

Formula help with removing duplicates while looking at other column criteria

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...

www.mrexcel.com

I tried to replicate what your answer was from here, but was unable to apply the same principles to the new issue.