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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

tactps

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

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,210
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,184
Messages
5,857,827
Members
431,900
Latest member
Paradime0346

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