# Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

## Click here to get your free Excelchat help session

Trusted by people who work at