Question description:
This user has given permission to use the problem statement for this
blog.
i have a table with Dates, First names , Last names and i want to count how many times a name occurs, but if a name occurs more than once on a particular date i need to only count it once. this is on excel and not google sheets
Solved by F. H. in 31 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
04/08/2018 - 05:09
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern. :)
User
04/08/2018 - 05:10
Hello!
User
04/08/2018 - 05:10
i have made a kinda of example i can post to you?
Excelchat Expert
04/08/2018 - 05:10
Yes, that would be awesome!
User
04/08/2018 - 05:11
so as you can see i have date and names
User
04/08/2018 - 05:11
in the 2nd table i have a total count but in the last column i want unique days occured
User
04/08/2018 - 05:12
so as an example Lima F has a total of 5 but only occurs on 3 days so i want that number of 3 in that last column
User
04/08/2018 - 05:13
is that possible?
Excelchat Expert
04/08/2018 - 05:13
Oh sorry, my computer hanged.
User
04/08/2018 - 05:13
np :)
Excelchat Expert
04/08/2018 - 05:14
Okay, I see what you mean here. Basically, you would want to count how many times a name is found, but they need to be counted only once for the same date. Is this correct? :)
User
04/08/2018 - 05:15
yes i want it to count only once if it occurs multiple times on the same day
Excelchat Expert
04/08/2018 - 05:15
Okay, noted on that. Let me work on it for a while and update you the soonest!
User
04/08/2018 - 05:15
thank you
User
04/08/2018 - 05:16
what happens if the time runs out?
Excelchat Expert
04/08/2018 - 05:16
Just a note however, it seems we will need to use a helper column to aide us in our formula. :)
User
04/08/2018 - 05:16
ok whats that?
Excelchat Expert
04/08/2018 - 05:16
Oh, we can actually extend for 20 minutes more when the timer reaches 3 minutes. I'll explain the helper column once I'm finished with the solution. :)
User
04/08/2018 - 05:16
ok thank you
Excelchat Expert
04/08/2018 - 05:17
I may not be responsive for a while, btw, since I'll be trying this out in Excel, not in the document preview. But rest assured that I'll go back to you once done.
User
04/08/2018 - 05:17
ok
User
04/08/2018 - 05:19
not sure if this will affect your solution but i need this to work for a much larger range....about 500 - 1000 rows
User
04/08/2018 - 05:28
combining date and name?
Excelchat Expert
04/08/2018 - 05:28
Hi! If you look at column F, this will include our helper column. :)
Excelchat Expert
04/08/2018 - 05:28
Yes, that's true. That will aide us further in our main formula.
User
04/08/2018 - 05:28
great
Excelchat Expert
04/08/2018 - 05:29
Oh, by the way, are you working on this in Excel or google sheets? :)
User
04/08/2018 - 05:29
excel
Excelchat Expert
04/08/2018 - 05:29
Nice! Let me just upload to you the file containing the formula.
User
04/08/2018 - 05:30
great
Excelchat Expert
04/08/2018 - 05:30
Already done! Please look at column M for the formula. :)
[Uploaded an Excel file]
Excelchat Expert
04/08/2018 - 05:30
Basically, the formula is a combination of SUMPRODUCT that incorporates two COUNTIF functions. For cell M3, the formula is as follows: =SUMPRODUCT((($F$4:$F$33<>"")/COUNTIF($F$4:$F$33,$F$4:$F$33&""))*($C$4:$C$33=$J3)*($D$4:$D$33=$K3))
User
04/08/2018 - 05:31
is it possible if you can explain how it works?
Excelchat Expert
04/08/2018 - 05:31
Sure thing. First part of the formula is: =SUMPRODUCT((($F$4:$F$33<>"")/COUNTIF($F$4:$F$33,$F$4:$F$33&""))
Excelchat Expert
04/08/2018 - 05:32
This is referenced to our column F or helper column. Basically, what this does is to count all the unique values in our helper column.
User
04/08/2018 - 05:32
ok
Excelchat Expert
04/08/2018 - 05:32
However, the catch to this is that it counts ALL unique values for ALL names. Thus, we have the second part of the formula, which is *($C$4:$C$33=$J3)*($D$4:$D$33=$K3))
Excelchat Expert
04/08/2018 - 05:34
This scans column C and only considers J3 (Lima) in the count. Likewise, the other part scans column D and matches this to cell K3 (F) to ensure that we are only counting for Lima F.
User
04/08/2018 - 05:34
ok
Excelchat Expert
04/08/2018 - 05:34
Since you're going to work on many rows, you will need to adjust the reference ranges of the formula.
Excelchat Expert
04/08/2018 - 05:34
This will depend on how many rows that you have. :)
User
04/08/2018 - 05:34
yeah of course :)
Excelchat Expert
04/08/2018 - 05:35
Nice! Do you have any more clarifications with the provided solution? :)
User
04/08/2018 - 05:35
i do
User
04/08/2018 - 05:35
COUNTIF($F$4:$F$33,$F$4:$F$33&"")
User
04/08/2018 - 05:35
this part
User
04/08/2018 - 05:36
we countif the range $F$4:$F$33 has the criteria of $F$4:$F$33&""
Excelchat Expert
04/08/2018 - 05:36
Oh I see. COUNTIF works differently within the SUMPRODUCT function. Basically, what that COUNTIF actually produce is an array.
User
04/08/2018 - 05:36
why do we have to add &"" after that last part?
User
04/08/2018 - 05:36
oh ok
Excelchat Expert
04/08/2018 - 05:37
we need that since it's supposed to protect our data if any of the range is in fact "".
User
04/08/2018 - 05:38
im not sure i understand can you provide an example?
User
04/08/2018 - 05:38
do you mean if the range is a blank cell?
User
04/08/2018 - 05:38
well part of the range
Excelchat Expert
04/08/2018 - 05:39
Yes. :) It considers the scenario that a blank cell is within the selected range.
User
04/08/2018 - 05:39
ah ok
Excelchat Expert
04/08/2018 - 05:40
It's actually pretty complicated to explain as the SUMPRODUCT performs as an array function, thus the COUNTIF produces this array of values, and is not in fact just a simple single value.
User
04/08/2018 - 05:40
Thank you so much for you help!
Excelchat Expert
04/08/2018 - 05:40
Nice! You can now end the session via the button on your upper right (sorry, can't do it from our end). I would also appreciate it if you can leave a great feedback after this.Thanks so much for using Got it pro and have a nice day! :)
User
04/08/2018 - 05:41
oh i will you have been BRILLIANT!!!
User
04/08/2018 - 05:41
THANK YOU SO MUCH!!
Excelchat Expert
04/08/2018 - 05:41
Thanks!
User
04/08/2018 - 05:41
have a great day!
Excelchat Expert
04/08/2018 - 05:41
Hoping for your great comment on this. Have a nice day! :)
User
04/08/2018 - 05:41
:D
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
