Counting & Summing With Iqnore Duplicate Names

[Jhon Mustofa]

hi all...

i need your help, how to counting and summing data with contains duplicate name people
i want to know : (iqnore duplicate)
- count data in grade a, grade b and grade c
- sum data in grade a, grade b and grade c

note : the column "name" contains duplicate names...
please, check my file...

any help, much appreciated..

john m

[Richard Buttrey]

Hi,

See attached where I added a unique list of names in column J and then SUMIF() and COUNTIF formulae alongside.

[Ochimus]

Jhon,

What do you want to happen where a duplicated name has the same value on different dates?

"ALIMUDDIN" has a Grade C score of 181,818 on 24/05/2016, and again on 03/10/2016. Do you want to count both of them, so his total is 363,636? Or just once, so he has 181,818?

What do you then want where a duplicated name has different values on different dates as well?

"FATIMAH" has three Grade A scores of 544,554 (2 on 01/01/16 and one on 02/01/2016) then another 500,000 on 24/05/2016, and 1,000,000 on 02/06/2016

Do you count all of them, so her Grade A score is 3,133,662, or count only values that are different, so she has (544,554 + 500,000 + 1,000,000)=2,044,554

Ochimus

[Jhon Mustofa]

thanks Ricard, but not fully working....
the value always keep 1 not 2,3,4 etc....

for grade a, total sum value is 27.106.339 (is correct) and i want find counting data's name ignore duplicate...

for easy understanding :

in grade a, count unique name is 26 ? How to find 26 using formula in pivot?

in grade a, the result should be 27.106.339 (sum) and 26 (count).

sorry, i hope you not create new table but continue the table before...

[Glenn Kennedy]

To return the number of unique values, use this array formula, copied across:

=SUM(--(FREQUENCY(IF(D$2:D$800>0,MATCH($B$2:$B$800,$B$2:$B$800,0)),ROW($B$2:$B$800)-ROW($B$2)+1)>0))

to return the sum, use this, copied across (assuming that I understand your request)
=SUM(D:D)

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Jhon Mustofa]

hi glenn, it works..

i have a problem, how to make it in pivot formula?
it's possible if the unique values can breakdown in a list?
like i mean for unique data representative is 1 and 0 for duplicate value...
for grade a = 26, i want to know, who is that?

i hope you could help me...

[Glenn Kennedy]

You can return a list of unique names using an array formula like this, copied across and down:

=IFERROR(INDEX($B$2:$B$800,MATCH(0,INDEX(IF(D$2:D$800<>0,COUNTIF(J$4:J4,$B$2:$B$800)),0),0)),"")

but to make a Pivot Table DIRECTLY from the data set that you have, you would need Excel 2016.

See:


https://blogs.office.com/2015/12/15/...in-excel-2016/



Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Jhon Mustofa]

thank you so much....Glen