Sum formula that ignores duplicates / Frequency Formula

vane0326

Well-known Member
Joined
Aug 29, 2004
Messages
819
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
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
 

vane0326

Well-known Member
Joined
Aug 29, 2004
Messages
819
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?

Please let me know.
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203

ADVERTISEMENT

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?

Your exhibit shows:

{=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
 

vane0326

Well-known Member
Joined
Aug 29, 2004
Messages
819
Hi Aladin Akyurek,

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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
vane0326 said:
Hi Aladin Akyurek,

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

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