Sum formula that ignores duplicates / Frequency Formula

[vane0326]

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
	A	B	C	D	E	F	G	H	I	J
1			Last	First		STD	Actual	Crew
2	SHIFT	Clock	Name	Name	Product#	Oper.#	Oper.#	Size	Qty
3	1	2107	SALDIVAR	ROB	753984-0002	400915	400915	1	16
4	1	2107	SALDIVAR	ROB	753984-0003	400915	400915	1	8
5	1	2107	SALDIVAR	ROB	753984-0001	400915	400915	1	7
6		2107 Total
7
8
9	2	4475	SOLIS	DAVID	K272-188	400910	400910	1	40
10	2	4475	SOLIS	DAVID	K058-519R	400910	400910	1	10
11		4475 Total
12
13	2	4000	CASTILLO	JEFF	442733-0011	400820	400815	2	80
14	2	4000	CASTILLO	JEFF	449614-0002	400815	400820	2	467
15	2	4000	CASTILLO	JEFF	407657-0003	400820	400820	2	131
16		4000 Total
17
18	2	4535	CASTILLO	MATT	442733-0011	400820	400815	2	80
19	2	4535	CASTILLO	MATT	449614-0002	400815	400820	2	467
20	2	4535	CASTILLO	MATT	407657-0003	400820	400820	2	131
21		4535 Total
22									1437	759
23
Sheet1

 

[vane0326]

Bump*

 

[tactps]

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]

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
	A	B	C	D	E	F	G	H	I	J
1			Last	First		STD	Actual	Crew
2	SHIFT	Clock	Name	Name	Product#	Oper.#	Oper.#	Size	Qty
3	1	2107	SALDIVAR	ROB	753984-0002	400915	400915	1	16
4	1	2107	SALDIVAR	ROB	753984-0003	400915	400915	1	8
5	1	2107	SALDIVAR	ROB	753984-0001	400915	400915	1	7
6		2107 Total
7
8
9	2	4475	SOLIS	DAVID	K272-188	400910	400910	1	40
10	2	4475	SOLIS	DAVID	K058-519R	400910	400910	1	10
11		4475 Total
12
13	2	4000	CASTILLO	JEFF	442733-0011	400820	400815	2	80
14	2	4000	CASTILLO	JEFF	449614-0002	400815	400820	2	467
15	2	4000	CASTILLO	JEFF	407657-0003	400820	400820	2	131
16		4000 Total
17
18	2	4535	CASTILLO	MATT	442733-0011	400820	400815	2	80
19	2	4535	CASTILLO	MATT	449614-0002	400815	400820	2	467
20	2	4535	CASTILLO	MATT	407657-0003	400820	400820	2	131
21		4535 Total
22								759	1437	759
23
Sheet1

 

[Aladin Akyurek]

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]

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]

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.