Log In
Register
Home
March 20, 2014
Offline
I'm posting this question in the hope someone can help and offer a possible solution. I have attached a rough copy of the workbook I'm working with. Simply I have a workbook of people that call for an appointment for a date and time. The operator(s) place comments in the appointment comments sometimes several different comments for the same appointment number and time. I am trying to figure out how many appointments were made with the text "psmac" in the text field comment field. I don't want to count it twice if it is listed a few times under the same account number. Hope this makde sense.
So total appointmnets made based on the account number field. So that will give me the total appointments. Then of those total appointments from the account numbers I want to count the number of times "psmac" comment is in there and only count the individual account number one time each.
VIP
April 21, 2015
Offline
Sorry, but don't see an attachment?
Frans
March 20, 2014
Offline
Gary Kendrick said
I'm posting this question in the hope someone can help and offer a possible solution. I have attached a rough copy of the workbook I'm working with. Simply I have a workbook of people that call for an appointment for a date and time. The operator(s) place comments in the appointment comments sometimes several different comments for the same appointment number and time. I am trying to figure out how many appointments were made with the text "psmac" in the text field comment field. I don't want to count it twice if it is listed a few times under the same account number. Hope this makde sense.So total appointmnets made based on the account number field. So that will give me the total appointments. Then of those total appointments from the account numbers I want to count the number of times "psmac" comment is in there and only count the individual account number one time each.
March 20, 2014
Offline
Frans Visser said
Sorry, but don't see an attachment?Frans
I apologize for that. I didn't click that last button. Hope this might help.
VIP
Trusted Members
June 25, 2016
Offline
Hi Gary
See if this works.
1) Create a helper column E
2) In cell E2 enter =IF(ISNUMBER(SEARCH("PSMAC",D2)),IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2)>1,0,1),0)
3) Copy this formula down as many cells as needed. It will identify unique appointments with PSMAC (not case-sensitive)
4) Sum the helper column.
Hope this helps.
Sunny
1 Guest(s)
