# Calculate how many times member A visit the cafe

Closed
jeff - Apr 26, 2018 at 09:00 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Apr 26, 2018 at 11:46 AM
Hello,

member A register at cafe on a date 12 april 2017.
He visited the cafe every times started from the registed date when the cafe opened at these dates.
A table showing the date opened as
1. 3 apr
2. 14 apr
3. 28 apr
4. 5 may
5. 25 may
6. 28 may

i want the cell will show the value “5” （A visit 5 times）and cell turn to red cour！when i enter the 5th date A visit the cafe（which is 28 may）

thanks
Jeff

Related:

## 1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 26, 2018 at 11:46 AM
Hi Jeff,

Not sure how you want the end result to look like, but the following formula will count the number of times your table of dates, contains dates higher then a given data:
=COUNTIF(\$A\$1:\$A1,">="&\$E\$1)
Where A1:A6 is the table of dates and E1 is the specified date (or replace it with an actual date instead of cell reference). In this example place the formula in B1 and drag it down.

To color the cell, use conditional formatting. When you want to color the cell red when the above formula is placed in column B and gives a result of 5, then:
- Select column B.
- Go to conditional formatting, found under the Start ribbon and select add rule.
- Choose to use a formula: =\$B1=5, choose your Format.
- Hit OK when done.

Best regards,
Trowa