Countif with duplicate values

[jei eusebio]

Hi Ninjas,

I have a list of employee names on column A & their site in column B, the list of employee names in column A has a lot of duplicate values, how I can count the unique employee names per site? Thanks a lot!ü

Jei

 

[Somendra Misra]

Hi Jei,

Can you upload a sample file.

Regards,

 

[jei eusebio]

Hi Somendra .. I have attached the file. just something to add .. is it possible to count the names that only has data in them? in the file I have 3 time set to update the report .. say for example 9am, can we only count the unique names for 9am and not include the ones with 0:00 in column H? Thanks again!

 

[Somendra Misra]

@jei eusebio

Try in B6 below formula since you have one site location only.

=SUM(IF(FREQUENCY(IF(IFERROR((A6=Raw!$G$4:$G$1888)*(Raw!$H$4:$H$1888>0),0),MATCH(Raw!$A$4:$A$1888,Raw!$A$4:$A$1888,0)),IF(IFERROR((A6=Raw!$G$4:$G$1888)*(Raw!$H$4:$H$1888>0),0),MATCH(Raw!$A$4:$A$1888,Raw!$A$4:$A$1888,0))),1,0))

Note this is an array formula, so must be entered with Ctrl+Shift+Enter.

and by the way you other file is under process may be complete by tomorrow

Regards,

 

[jei eusebio]

It worked! Thank you very much .. specially with the other file.

If anyone is reading this, can you promote this guy to a super Ninja or something higher LOL .. he's alway's online everytime I post a question here. Thanks again! ^_^

 

[Somendra Misra]

Thanks for your kind words, and Welcome Back.

Regards,

 

[John()]

Hi Somendra Misra would it be possible to look at my effort on this as my result is different and im wondering did i make a mistake understanding the problem criteria
first off i created my own small sample on another workbook to see if i could do this and when i got it working downloaded jei sheet and ran my formula on raw sheet after making a adjustment to it (i used numbers for site names then saw he used names) so i added a helper colum to get numbers for names eg if yuama then 1
my formula then pulled out 232 indvidual names. which i thought was the answer required
then added your formula to B6 of Count sheet and it shows 208 so i new something was off in mine so reread criteria and realised i hadnt considered the time part eg if 0:00 dont count name
so added this to my formula and got 126 individual names which now is way different to your answer
so was wondering if you had time could you look at my interpetation and let me know what im doing wrong ... i went with if any cell in the convey 9am =0:00 dont count the name of the person for those cells .... and if the time is even small like 0:05 use that name.

i have coulms and results filled with colour and also have filters added to make it easy to understand what i done and how i reached my conclusion.

Thank you if you are able to do this as i am learning through working on some of the problems on this forum

 

[Somendra Misra]

Hi John,

The right answer is 116 where as you are getting 126, the difference of 10 is of the reason because you are counting those names also where there is a #n/a error in Site column and I had not counted them. Just see it.

Regards,

 

[NARAYANK991]

Hi John ,

There are 2 ways to approach this :

1. Use simple logic and simple formulae in helper columns ; the whole purpose of using helper columns is to have simple formulae in them.

2. Use pivot tables , since their calculations cannot be wrong provided the data is valid.

See the two attached files for these 2 different approaches ; in the first file , the formulae have been inserted in columns S , T and U.

Narayan

 

[Somendra Misra]

@NARAYANK991

Sir how about a pivot in the file attached. Just by dropping Different report timing in one at a time we can get different count.

Regards,

 

[NARAYANK991]

Hi Misra ,

I had used formulae in columns AG through AK , which probably you have not seen.

As far as your pivot table is concerned , I agree that is a good way of cross-checking , though I would use the formula :

=COUNTA(B4:B188)

which is too simple , I think. Your formula shows that it is doing a lot of work !

Narayan

 

[Somendra Misra]

@NARAYANK991

Sir I did saw the calculations columns in your sheets. The purpose of using such a heavy formula is because if you drag 12am in and remove 9am than no. of rows are dynamic. That was the reason to have one formula so that the user will not have to adjust the range in the formula.

Regards,

 

[NARAYANK991]

Hi Misra ,

I think pivot tables are used because users find it difficult to develop such formulae ; pivot tables are an essential skill in mastering Excel , and they are also a rough and ready means of cross-checking results obtained using more complex formulae , since a lot of users avoid pivot tables because they need to be refreshed when ever the input data changes.

My post was addressed to John ; one approach involved using simple formulae and helper columns which resulted in a total of 116 ; the other approach involved using a rough and ready pivot table , and again a few helper columns using simple formulae , which gave the same result of 116.

The whole point was that John could have verified everything using the skills he already had ; I have yet to come across a problem which cannot be solved using helper columns and simple Excel formulae , provided the correct logic is used. VBA is of course an able assistant.

Narayan

 

[Somendra Misra]

@NARAYANK991 Sir,

No offend to your method, you are very much knowledgeable and experienced to me, & I respect you a lot. I just want to show that Pivot table used by me was much simpler and does not use lot of calculation cells. I agree that lot's of end user find it difficult to adapt to changing situations in a complex formula.

Regards,

 

[NARAYANK991]

Hi Misra ,

Absolutely no offense taken ! There are any number of ways to achieve the same result , and everyone is free to accept which ever appeals to them.

Narayan

 

[John()]

Hi John,

The right answer is 116 where as you are getting 126, the difference of 10 is of the reason because you are counting those names also where there is a #n/a error in Site column and I had not counted them. Just see it.

Regards,

Thanks for taking the time to look at this for me ..... ye i totally missed the #n/a .. made a minor adjustement to my code (moved the site ref from within the colum that the formula checked to see if was a 1 to a cell off the table and locked reference to it) ... now i get the total of 116 ....

 

[John()]

Hi John ,

There are 2 ways to approach this :

1. Use simple logic and simple formulae in helper columns ; the whole purpose of using helper columns is to have simple formulae in them.

2. Use pivot tables , since their calculations cannot be wrong provided the data is valid.

See the two attached files for these 2 different approaches ; in the first file , the formulae have been inserted in columns S , T and U.

Narayan

Thanks for the response it helps to see other options .... your first option of the helper colums ... this is the way i did it initially on my practice .... had around 3 helper colums with different formulas to pull things apart to get the final result ... then i added all the formulas together to get one formula and removed the helper colums ...
The pivot table option i will have to study as i havent much experience with using pivot tables so im delighted you posted that as well ... was surprised to see the formulas as i just persumed the pivot table itself could pull the info required out (goes to show my lack of use of pivot tables )

 

[Somendra Misra]

Just to add; what if count is required for multiple Site location with three timing report as the OP original file shows, than how many helper cells will be required?

Regards,

 

[NARAYANK991]

Hi John ,

You are right that pivot tables can do a lot ; however , my experience with pivot tables is limited , and I found it easier to use helper columns outside the pivot table than to have calculated fields within the pivot table.

The point was that putting together all of this took just a few minutes , but that was enough to verify the results from Misra's formula , which I am sure must have taken more time. The point is that users post questions because they think they do not have the skills to solve them , when it is clear that most problems are within the grasp of almost everyone except an absolute novice ; if you can use helper columns to implement the right logic , you can solve most problems.

Narayan

 

[NARAYANK991]

Hi Misra ,

You will be surprised by how users use columns ; you might have seen a particular question where the user had used 365 columns , one for each day of the year !

Surely this problem will not require so many helper columns.

The issue is never the number of helper columns ; the issue is always whether the helper column is being used to implement the correct logic ; if the logic is right , the answer can never be wrong , though the converse holds true , which we have seen on several occasions ; the answer is right , but since the logic is wrong , with the proper data , it is found that the answer is wrong after all ; it is also possible that this may be found out much later , with more serious consequences.

Narayan

 

[Somendra Misra]

Hi Misra ,

You will be surprised by how users use columns ; you might have seen a particular question where the user had used 365 columns , one for each day of the year !

Surely this problem will not require so many helper columns.

The issue is never the number of helper columns ; the issue is always whether the helper column is being used to implement the correct logic ; if the logic is right , the answer can never be wrong , though the converse holds true , which we have seen on several occasions ; the answer is right , but since the logic is wrong , with the proper data , it is found that the answer is wrong after all ; it is also possible that this may be found out much later , with more serious consequences.

Narayan

"You will be surprised by how users use columns ; you might have seen a particular question where the user had used 365 columns , one for each day of the year !"

Lol ... hahahha...

You reminded of my boss.

Regards,