Excel function to count row once for two criteria found in same cell

Question

My excel knowledge is very basic using my own research here and there so I would need some help from you guys.

I have a situation where I need to calculate the number of rows that contain certain date values which I converted it to text for certain reason (didn’t want to list them in separate rows).

E.g.

Col A. Col B.

Customer Name, Date Purchase

Andy. 1/2/2021 , 2/3/2021

Jenny. 2/2/2021 , 3/4/2021

Mary. 1/3/2021 , 1/5/2021

I want to count the number of unique customers who purchased between February and May 2021 for the above case, but I didn’t know how I should proceed from an existing countifs formula I had that looks like the following:

=countif(B:B, “/2/2021”) + countif(B:B, “/3/2021”) + countif(B:B, “/4/2021”) + countif(B:B, “/5/2021”)

This formula had returned the value as 9 based on the following results

February : 2

March : 2

April : 4

May : 1

which I didn’t want as I want the count number of different customers I had instead (for this case I want the cell with a formula that can return the result as “3” , not the number of time purchased).

Please help me to make a suitable formula works! Thanks 🙏

Answer 1

By adding an indicator in Column C that shows whether or not each customer satisfies your purchase requirements you could simply sum these up at the bottom to count the number of unique customers.

Hopefully the image below containing the formula in Column C (essentially checking that each customer has at least one purchase between February and May) will resolve your issue. I added a Dummy customer that doesn't satisfy these dates just a check so the total unique customers is still 3 (Cell C6 which uses a formula of '=COUNTIF(C2:C5,"Y")').

You mention the possibility of having 100 purchase dates, are these all recorded in text format in Column B? I would have thought that would make things more difficult to track than having 100 individual columns (though this doesn't affect your query above).