Subtotal amounts only once if there are duplicates

Worker8ee

New Member
Joined
Aug 8, 2018
Messages
28
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.

Capture.PNG
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,450
Office Version
  1. 365
Platform
  1. 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)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,752
Here's the inefficient and impractical formula:

Book1 (version 1).xlsb
ABCDEFGHI
1
238
3
4518-May24-MayGrocery11
5518-May24-MayGrocery11
6518-May24-MayGrocery11
7318-May24-MayDairy6
8318-May24-MayDairy6
9318-May24-MayDairy6
10218-May24-MayGrocery5
11218-May24-MayGrocery5
12218-May24-MayGrocery5
13318-May24-MayMeat3
14318-May24-MayMeat3
15318-May24-MayMeat3
16318-May24-MayMeat3
17318-May24-MayMeat3
1851-Jun7-JunGrocery7
1951-Jun7-JunGrocery7
2051-Jun7-JunGrocery7
2131-Jun7-JunDairy2
2231-Jun7-JunDairy2
2331-Jun7-JunDairy2
2421-Jun7-JunGrocery1
2521-Jun7-JunGrocery1
2621-Jun7-JunGrocery1
2731-Jun7-JunMeat3
2831-Jun7-JunMeat3
2931-Jun7-JunMeat3
3031-Jun7-JunMeat3
3131-Jun7-JunMeat3
Sheet6
Cell Formulas
RangeFormula
I2I2=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!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,450
Office Version
  1. 365
Platform
  1. Windows
Here's the inefficient and impractical formula:
A very good effort thought sir, I tried a couple of things but gave up long before I got anywhere close!
 

Worker8ee

New Member
Joined
Aug 8, 2018
Messages
28
Haha nice Eric W, it's informative so thanks for taking the time to post that, I will heed your advice!


Here's the inefficient and impractical formula:

Book1 (version 1).xlsb
ABCDEFGHI
1
238
3
4518-May24-MayGrocery11
5518-May24-MayGrocery11
6518-May24-MayGrocery11
7318-May24-MayDairy6
8318-May24-MayDairy6
9318-May24-MayDairy6
10218-May24-MayGrocery5
11218-May24-MayGrocery5
12218-May24-MayGrocery5
13318-May24-MayMeat3
14318-May24-MayMeat3
15318-May24-MayMeat3
16318-May24-MayMeat3
17318-May24-MayMeat3
1851-Jun7-JunGrocery7
1951-Jun7-JunGrocery7
2051-Jun7-JunGrocery7
2131-Jun7-JunDairy2
2231-Jun7-JunDairy2
2331-Jun7-JunDairy2
2421-Jun7-JunGrocery1
2521-Jun7-JunGrocery1
2621-Jun7-JunGrocery1
2731-Jun7-JunMeat3
2831-Jun7-JunMeat3
2931-Jun7-JunMeat3
3031-Jun7-JunMeat3
3131-Jun7-JunMeat3
Sheet6
Cell Formulas
RangeFormula
I2I2=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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,556
Messages
5,625,495
Members
416,112
Latest member
somenka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top