Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Hi - I need a formula that can look at a Member Number (Column A) and report back if location (Column B) matches 1 of 6 locations, then looks to the date of occurance (Column N) but only adds back 1 value of repeating dates. I have this, but it is not coming back with the right answer... =SUMPRODUCT(('2017'!I$3:I$10000=$A223)*ISNUMBER(SEARCH({"2","6","58","69","76","97"},'2017'!N$3:N$10000))) Information is corporate private, so I won't be uploading file.
Solved by S. L. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 28/02/2018 - 08:07
hello
Excelchat Expert 28/02/2018 - 08:07
Would you p;ease show sample data?
User 28/02/2018 - 08:09
Column A is on the sheet with the formula...Column B and C are on Tab "2017".
User 28/02/2018 - 08:10
If I filter for customer 144543 on tab "2017" then the answer for the formula I have above should be 5, it is coming back with 22
Excelchat Expert 28/02/2018 - 08:11
would you please state the problem now again?
Excelchat Expert 28/02/2018 - 08:11
I mean if that's not a problem for you. '
User 28/02/2018 - 08:12
I can't get it to recognize 1st the specific location criteria(B) and then recognize the date(C), but only add one date if multiple of the same.
Excelchat Expert 28/02/2018 - 08:13
There are other ways but I am not able to get a grasp of the problem yet.
User 28/02/2018 - 08:14
like location 6 for this customer, the answer would be 5. 5 times that we dealt with them. Even though there are 10 dates, there are only 5 that are counted, not repeating
Excelchat Expert 28/02/2018 - 08:14
You are trying to match member number first, then location, then if those are satisfied, return date?
User 28/02/2018 - 08:14
yes, but only date once. like yoiu can see 20171211 comes up 4 times...I only want it to count one of those
Excelchat Expert 28/02/2018 - 08:15
Sorry, do you want to get the count of dates or want to return the actual dates?
User 28/02/2018 - 08:16
just a count...So looking at location 6 there are 10 results from C9-C18 but only 5 of those are non repeating
Excelchat Expert 28/02/2018 - 08:18
Okay, would you mind replicating the orientation of the original file?
User 28/02/2018 - 08:18
not sure what you mean sorry
Excelchat Expert 28/02/2018 - 08:19
I mean present the sample data as like they are in the original file.
Excelchat Expert 28/02/2018 - 08:20
Thanks.
User 28/02/2018 - 08:21
im working on Analysis tab in cell L on here
Excelchat Expert 28/02/2018 - 08:23
Please select yes when you are asked.
Excelchat Expert 28/02/2018 - 08:34
Please stay with me while I'm working on this.
User 28/02/2018 - 08:34
ok
Excelchat Expert 28/02/2018 - 08:36
You said something about one of the 6 locations.
Excelchat Expert 28/02/2018 - 08:36
Sorry, its a complicated questions and I might need you to state from the beginning again.
User 28/02/2018 - 08:37
yes, this is a small sample of members. This one happens to only pull from the #6, but some can pull from the other 6 I listed.
User 28/02/2018 - 08:37
Locations are in parenthesis....
User 28/02/2018 - 08:37
Here is the original post
User 28/02/2018 - 08:37
I need a formula that can look at a Member Number (Column A) and report back if location (Column B) matches 1 of 6 locations, then looks to the date of occurance (Column N) but only adds back 1 value of repeating dates. I have this, but it is not coming back with the right answer... =SUMPRODUCT(('2017'!I$3:I$10000=$A223)*ISNUMBER(SEARCH({"2","6","58","69","76","97"},'2017'!N$3:N$10000)))
Excelchat Expert 28/02/2018 - 08:37
The statement confuses me big time.
User 28/02/2018 - 08:38
Ok...
User 28/02/2018 - 08:38
On Analysis tab, I am getting it to look up Member Number which is A on your sample.
Excelchat Expert 28/02/2018 - 08:39
okay..
User 28/02/2018 - 08:40
It will then go to tab 2017 and search for the member number, when it comfirms, then it will search through the 6 potential lcoations (2, 6, 58, 69, 76, 97) if those match then it will move to the date column and count back the dates those happen but only once if repeating
Excelchat Expert 28/02/2018 - 08:42
I see. The 6 locations condition make it a bit complicated. Please select YES again.
User 28/02/2018 - 08:42
agreed...tough one :)
Excelchat Expert 28/02/2018 - 08:43
Thanks for the patience.
Excelchat Expert 28/02/2018 - 08:52
=SUMPRODUCT((A2:A7=D2)*(C2:C7<>"")*(B2:B7=D3)/COUNTIF(C2:C7,C2:C7&""))
Excelchat Expert 28/02/2018 - 08:52
This will give you count for one location.
Excelchat Expert 28/02/2018 - 08:53
Then you can sum the result for other 5 locations.
Excelchat Expert 28/02/2018 - 08:55
We can simplify it further if you don't have empty cells in date column by this formula.
Excelchat Expert 28/02/2018 - 08:55
=SUMPRODUCT((A2:A7=D2)*(B2:B7=D3)/COUNTIF(C2:C7,C2:C7))
User 28/02/2018 - 08:55
that's pretty manual for locations with 1000+ members...
User 28/02/2018 - 08:55
I dont want the dates summed, that won't come back with only 1 if there are multiple same dates....
User 28/02/2018 - 08:56
again, too many members...
Excelchat Expert 28/02/2018 - 08:56
dates are not summed, you are getting count of unique dates.
Excelchat Expert 28/02/2018 - 08:57
AND for the location, if its too much, you can go with VBA.
Excelchat Expert 28/02/2018 - 08:57
I'm afraid formula will fail you.
User 28/02/2018 - 08:57
ok, i will have to tweak it to my cells and try...
User 28/02/2018 - 08:57
thanks
Excelchat Expert 28/02/2018 - 08:58
Sure, I'm here for another 9 minutes, after that we will reach the maximum session time.
User 28/02/2018 - 08:58
ok, I have to leave...I have to do other work before I go home :)
Excelchat Expert 28/02/2018 - 08:59
Okay.
Excelchat Expert 28/02/2018 - 08:59
Another thing, it was silly from me. You can add multiple locations inside the sumproduct.
Excelchat Expert 28/02/2018 - 09:00
=SUMPRODUCT((A2:A7=D2)*(B2:B7=6)*(B2:B7=690)/COUNTIF(C2:C7,C2:C7))
Excelchat Expert 28/02/2018 - 09:00
Just like that.
User 28/02/2018 - 09:01
ok thx
Excelchat Expert 28/02/2018 - 09:01
Thanks for your patience and thanks again for trying out excel got it pro.
Excelchat Expert 28/02/2018 - 09:01
Have a great day!
User 28/02/2018 - 09:02
thx you too

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.