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.

 

[dreid1011]

See if this does what you want.. however, I should note, I did not subtract any 'Canceled' values, I just did not include them in the sum. Looking at your summary sheet, it looks like you are taking care of actually subtracting them there.

=LET(u,UNIQUE(Jan!$A$3:$W$10,FALSE,FALSE),SUM(TAKE(FILTER(u,CHOOSECOLS(u,2)<>"Canceled"),,-1)))

 

[dreid1011]

Or a trusty SUMPRODUCT ...

=SUMPRODUCT((1/COUNTIF(Jan!A3:A10,A3:A10))*(Jan!B3:B10<>"Canceled")*(Jan!W3:W10))

 

[samide2001]

See if this does what you want.. however, I should note, I did not subtract any 'Canceled' values, I just did not include them in the sum. Looking at your summary sheet, it looks like you are taking care of actually subtracting them there.

=LET(u,UNIQUE(Jan!$A$3:$W$10,FALSE,FALSE),SUM(TAKE(FILTER(u,CHOOSECOLS(u,2)<>"Canceled"),,-1)))

The result of this is formula is $23.43. For some reason the formula doesn't remove the duplicates from column A, which has to happen before I can subtract any canceled orders.

 

[samide2001]

Or a trusty SUMPRODUCT ...

=SUMPRODUCT((1/COUNTIF(Jan!A3:A10,A3:A10))*(Jan!B3:B10<>"Canceled")*(Jan!W3:W10))

This resulted divide by zero error.

 

[Fluff]

How about

=LET(u,UNIQUE(Jan!A3:A10),SUM(SUMIFS(Jan!W:W,Jan!A:A,u,Jan!C:C,"<>canceled")/COUNTIFS(Jan!A:A,u)))

 

[dreid1011]

The result of this is formula is $23.43. For some reason the formula doesn't remove the duplicates from column A, which has to happen before I can subtract any canceled orders.

This resulted divide by zero error.

Are you using these on larger data sets than what you provided as a sample? Perhaps there is something else in the data now that was not represented which may be throwing off the calculations?

Or see if Fluff's suggestion solves the issue.

 

[samide2001]

How about

=LET(u,UNIQUE(Jan!A3:A10),SUM(SUMIFS(Jan!W:W,Jan!A:A,u,Jan!C:C,"<>canceled")/COUNTIFS(Jan!A:A,u)))

This works for the data that was provided. How can I change the first unique range to look at more than A3:a10 in future formulas? I won't be the end user of the spreadsheet, so I need something that will be user-friendly for the end-user.

 

[Fluff]

How about

=LET(u,UNIQUE(filter(Jan!A3:A1000,Jan!A3:A1000<>"")),SUM(SUMIFS(Jan!W:W,Jan!A:A,u,Jan!C:C,"<>canceled")/COUNTIFS(Jan!A:A,u)))

 

[samide2001]

How about

=LET(u,UNIQUE(filter(Jan!A3:A1000,Jan!A3:A1000<>"")),SUM(SUMIFS(Jan!W:W,Jan!A:A,u,Jan!C:C,"<>canceled")/COUNTIFS(Jan!A:A,u)))

That works!