# Subtotal amounts only once if there are duplicates

#### Worker8ee

##### New Member
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.

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### jasonb75

##### Well-known Member
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)

#### Worker8ee

##### New Member
Thank you jasonb75! That worked perfectly!

#### Eric W

##### MrExcel MVP
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
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
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!

Replies
6
Views
275
Replies
1
Views
94
Replies
20
Views
417
Replies
7
Views
144
Replies
4
Views
332

1,127,092
Messages
5,622,653
Members
415,916
Latest member
eugenia

### 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?

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