Suppose that I have list of all the prescriptions we got for the year.
If I pick up more than one medication on a trip to that a pharmacy, then my table will show multiple rows with the same date and the same pharmacy name, changing only the medication name in those rows.
I want to count the number of times I went to each pharmacy. A simple count function would work, except if we got multiple medications that day, it may (count) list that pharmacy 3 times (one for each row - remember each row only list one medication) and in Column A it would list the same date 3 times. So when I actually only went their once, it may return an answer of 3, if I just count the column with the pharmacy names.
So how can I count the number of times I went to CVS (e.g eliminating the duplicate dates listed in column A, for that pharmacy when I picked up more the medication on that trip.. It would be a function maybe that returns a count for that pharmacy name, only if it's a unique date that hasn't been counted before.
You can do this :
=SUM(IF(FREQUENCY(IF((LEN(DATES & PHARMA) * (PHARMA=E13)) >0,MATCH(DATES & PHARMA,DATES & PHARMA,0),""), IF((LEN(DATES & PHARMA) * (PHARMA=E13)) >0,MATCH(DATES & PHARMA,DATES & PHARMA,0),""))>0,1,0))
In the formula below E13 was the first row in your sample book. Once you have pasted and then have pressed CTRL + SHIFT + ENTER, drag it down to cover rest of pharma
Thanks to rizvisa1 for this tip on the forum.