Sum formula that ignores duplicates / Frequency Formula

vane0326

Well-known Member
I dont how to write this.

Please look at cell I22 it sums up in that column BUT if you look at cell J22 thats the correct answer because Employees 4000 & 4535 worked together as a crew. So reality thoses employess you see there in my example produce only 759 Qty. This is a sample data. My data fluctuates.

If anyone knows how to sum up the data but exclude duplicates vaules ?
Needs SUM(IF(Frequency.xls
ABCDEFGHIJ
1LastFirstSTDActualCrew
2SHIFTClockNameNameProduct#Oper.#Oper.#SizeQty
312107SALDIVARROB753984-0002400915400915116
412107SALDIVARROB753984-000340091540091518
512107SALDIVARROB753984-000140091540091517
62107 Total
7
8
924475SOLISDAVIDK272-188400910400910140
1024475SOLISDAVIDK058-519R400910400910110
114475 Total
12
1324000CASTILLOJEFF442733-0011400820400815280
1424000CASTILLOJEFF449614-00024008154008202467
1524000CASTILLOJEFF407657-00034008204008202131
164000 Total
17
1824535CASTILLOMATT442733-0011400820400815280
1924535CASTILLOMATT449614-00024008154008202467
2024535CASTILLOMATT407657-00034008204008202131
214535 Total
221437759
23
Sheet1

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Bump*

Try:
=SUMPRODUCT(\$I\$2:\$I\$19/IF(\$E\$2:\$E\$19=0,1,COUNTIF(\$E\$2:\$E\$19,\$E\$2:\$E\$19)))

Enter with Shift-Ctrl-Enter

tactps your formula works but you gave me an idea so I got this formula into it and works also.

=SUMPRODUCT((I3:I20<>"")/COUNTIF(I3:I20,I3:I20&""),I3:I20)

If you can What does this means in the formula (I3:I20,I3:I20&"") is looking at the vaules as text before summing it because is that how its reading it as duplicates?

Sum excludes duplicates vaules.xls
ABCDEFGHIJ
1LastFirstSTDActualCrew
2SHIFTClockNameNameProduct#Oper.#Oper.#SizeQty
312107SALDIVARROB753984-0002400915400915116
412107SALDIVARROB753984-000340091540091518
512107SALDIVARROB753984-000140091540091517
62107 Total
7
8
924475SOLISDAVIDK272-188400910400910140
1024475SOLISDAVIDK058-519R400910400910110
114475 Total
12
1324000CASTILLOJEFF442733-0011400820400815280
1424000CASTILLOJEFF449614-00024008154008202467
1524000CASTILLOJEFF407657-00034008204008202131
164000 Total
17
1824535CASTILLOMATT442733-0011400820400815280
1924535CASTILLOMATT449614-00024008154008202467
2024535CASTILLOMATT407657-00034008204008202131
214535 Total
227591437759
23
Sheet1

vane0326 said:
tactps your formula works but you gave me an idea so I got this formula into it and works also.

=SUMPRODUCT((I3:I20<>"")/COUNTIF(I3:I20,I3:I20&""),I3:I20)

If you can What does this means in the formula (I3:I20,I3:I20&"") is looking at the vaules as text before summing it because is that how its reading it as duplicates?

{=SUMPRODUCT(\$I\$3:\$I\$20/IF(\$E\$3:\$E\$20=0,1,COUNTIF(\$E\$3:\$E\$20,\$E\$3:\$E\$20)))}

which can be replaced with:

=SUMPRODUCT((E3:E20<>"")/COUNTIF(E3:E20,E3:E20&""),I3:I20)

Needs just enter.

But what happens if C18 is VANE, that is, VANE and MATT also produces 442733-0011 as do CASTILLO and JEFF?

For the explanation of the formula for the distinct count, see my post in:

http://www.mrexcel.com/board2/viewtopic.php?t=37550

Is there a sample that you can provide a double critera in this formula ?

=SUMPRODUCT((E3:E20<>"")/COUNTIF(E3:E20,E3:E20&""),I3:I20)

So it does not have to look in one column it could look in 2 columns or maybe 3.

vane0326 said:

Is there a sample that you can provide a double critera in this formula ?

=SUMPRODUCT((E3:E20<>"")/COUNTIF(E3:E20,E3:E20&""),I3:I20)

So it does not have to look in one column it could look in 2 columns or maybe 3.

Either:

J3:

=C3&"#"&D3&"#"&E3

copied down so that you can invoke:

=SUMPRODUCT((J3:J20<>"")/COUNTIF(J3:J20,J3:J20&""),I3:I20)

to effect a dependable calculation;

Or: Switch to morefunc.xll's COUNTDIFF...

=COUNTDIFF(IF(C3:C20&"#"&D3:D20&"#"&E3:E20<>"",I3:I20,0),FALSE,0)

which must be confirmed with control+shift+enter.

The latter can be easily extended to more conditions.

Replies
6
Views
422
Replies
7
Views
316
Replies
6
Views
1K
Replies
1
Views
2K
Replies
20
Views
1K

1,216,381
Messages
6,130,295
Members
449,570
Latest member
TomMacca52

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.

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

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