Count lines that contain at least one date referring to the month of January

instrumatic

The sheet contains a list of customers and dates of visits in several columns. 
I need to count how many customers were visited in a given month. 
Some customers may have been visited more than once in a month, however they can only be counted once.
I'm not able to create a formula that does this. Could anyone help me?

Mark.poole

@instrumatic

Create you a helper column that looks at created date and the customer ID. This will auto count starting from the most recent visit. Then you can count how many times the number 1 shows up with in a given month.

Below I have provided a sample formula for you with the following assumptions.
1. you have a created Date column "system column that gives the date time stamp"
2. You have a "Helper" column that contains the month numbers or names.
3. You have a column that contains a unique way to identify your customers.

=COUNTIFS(Date:Date, >= Date@row, Month:Month, Month@row, [Customer ID]:[Customer ID], [Customer ID]@row)

Replace, Date, Month, and Customer ID with your column names.

You may also want to account for year. In which case create a column that has the following formula. I will call this one Year

=YEAR(Date@row)

Then adjust the formula as follows

=COUNTIFS(Date:Date, >= Date@row, Year:Year, Year@row, Month:Month, Month@row, [Customer ID]:[Customer ID], [Customer ID]@row)

Paul Newcome

You can also do this without helper columns by using something along the lines of

=COUNT(DISTINCT(COLLECT(Customers:Customers, Date:Date, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2025)))