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.
