I'm trying to figure out how to subtotal sum the units of a department only once if they are duplicated. Column I shows the complete department (column D) units for the date range shown in columns B & C, cells I4 through I6 are duplicate values of the same total which is 11 for the Grocery Department, cells I7 through I9 are the duplicate values of the same total which is 6 for the Dairy Department, etc. The formula in cell I2 is currently '=SUBTOTAL(109,I4:I31)' which sums anything that is not filtered but I can't figure out what to add to this formula so that it only sums the department total once and skips over any duplicate values, the desired formula would return 38 instead of 126. I've searched the forums and cannot seem to find anything regarding this so any help is greatly appreciated.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Subtotal amounts only once if there are duplicates
- Thread starter Worker8ee
- Start date
jasonb75
Well-known Member
- Joined
- Dec 30, 2008
- Messages
- 15,507
- Office Version
- 365
- Platform
- Windows
You'll need a helper column for that, it might be possible with a single formula but if it is, it would most likely be inefficient and impractical.
In J4 and fill down (if using a column other than J, remember to adjust the references to column J in the formulas to match the location of the formula).
=IF(COUNTIFS(D$3:D3,D4,B$3:B3,B4,C$3:C3,C4,J$3:J3,"<>0")=0,SUBTOTAL(109,I4),0)
In I2
=SUM(J4:J31)
In J4 and fill down (if using a column other than J, remember to adjust the references to column J in the formulas to match the location of the formula).
=IF(COUNTIFS(D$3:D3,D4,B$3:B3,B4,C$3:C3,C4,J$3:J3,"<>0")=0,SUBTOTAL(109,I4),0)
In I2
=SUM(J4:J31)
Upvote
0
Eric W
MrExcel MVP
- Joined
- Aug 18, 2015
- Messages
- 12,977
Here's the inefficient and impractical formula:
Seriously, stick with jasonb75's plan!
| Book1 (version 1).xlsb | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | |||||||||||
| 2 | 38 | ||||||||||
| 3 | |||||||||||
| 4 | 5 | 18-May | 24-May | Grocery | 11 | ||||||
| 5 | 5 | 18-May | 24-May | Grocery | 11 | ||||||
| 6 | 5 | 18-May | 24-May | Grocery | 11 | ||||||
| 7 | 3 | 18-May | 24-May | Dairy | 6 | ||||||
| 8 | 3 | 18-May | 24-May | Dairy | 6 | ||||||
| 9 | 3 | 18-May | 24-May | Dairy | 6 | ||||||
| 10 | 2 | 18-May | 24-May | Grocery | 5 | ||||||
| 11 | 2 | 18-May | 24-May | Grocery | 5 | ||||||
| 12 | 2 | 18-May | 24-May | Grocery | 5 | ||||||
| 13 | 3 | 18-May | 24-May | Meat | 3 | ||||||
| 14 | 3 | 18-May | 24-May | Meat | 3 | ||||||
| 15 | 3 | 18-May | 24-May | Meat | 3 | ||||||
| 16 | 3 | 18-May | 24-May | Meat | 3 | ||||||
| 17 | 3 | 18-May | 24-May | Meat | 3 | ||||||
| 18 | 5 | 1-Jun | 7-Jun | Grocery | 7 | ||||||
| 19 | 5 | 1-Jun | 7-Jun | Grocery | 7 | ||||||
| 20 | 5 | 1-Jun | 7-Jun | Grocery | 7 | ||||||
| 21 | 3 | 1-Jun | 7-Jun | Dairy | 2 | ||||||
| 22 | 3 | 1-Jun | 7-Jun | Dairy | 2 | ||||||
| 23 | 3 | 1-Jun | 7-Jun | Dairy | 2 | ||||||
| 24 | 2 | 1-Jun | 7-Jun | Grocery | 1 | ||||||
| 25 | 2 | 1-Jun | 7-Jun | Grocery | 1 | ||||||
| 26 | 2 | 1-Jun | 7-Jun | Grocery | 1 | ||||||
| 27 | 3 | 1-Jun | 7-Jun | Meat | 3 | ||||||
| 28 | 3 | 1-Jun | 7-Jun | Meat | 3 | ||||||
| 29 | 3 | 1-Jun | 7-Jun | Meat | 3 | ||||||
| 30 | 3 | 1-Jun | 7-Jun | Meat | 3 | ||||||
| 31 | 3 | 1-Jun | 7-Jun | Meat | 3 | ||||||
Sheet6 | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I2 | I2 | =SUM(IFERROR((MMULT(IF(((SUBTOTAL(103,OFFSET(A4,ROW(A4:A31)-ROW(A4),0))*A4:A31)=TRANSPOSE(A4:A31))*(B4:B31=TRANSPOSE(B4:B31))*(C4:C31=TRANSPOSE(C4:C31))*(D4:D31=TRANSPOSE(D4:D31)),I4:I31,0),ROW(A4:A31)^0))/(MMULT(IF(((SUBTOTAL(3,OFFSET(A4,ROW(A4:A31)-ROW(A4),0))*A4:A31)=TRANSPOSE(A4:A31))*(B4:B31=TRANSPOSE(B4:B31))*(C4:C31=TRANSPOSE(C4:C31))*(D4:D31=TRANSPOSE(D4:D31)),1,0),ROW(A4:A31)^0))^2,0)) |
| Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces. | ||
Seriously, stick with jasonb75's plan!
Upvote
0
Haha nice Eric W, it's informative so thanks for taking the time to post that, I will heed your advice!
Upvote
0
Similar threads
- Question
