Automatically calculate number of overdue cells in a column?

[youngxvato]

Hello,

I am trying to figure out how to automatically calculate the number of red cells in a column into rows 74/75. I have been having to input them manually and would like it to calculate on its own. Screenshot attached for better understanding as I am not entirely sure how to explain it.

Any help would be greatly appreciated!

 

[awoohaw]

You can't count cells by their formatting.
What is the conditional formatting rule for the column?

if the formatting rule is something like:
DueDate < CurrentDate

Assuming the two columns below are same length (or one is just cell length of 1):
then your counting rule would be:
=SUM(--(DueDateRange < CurrentDate))

 

[AhoyNC]

Here is a link that shows a short VBA code to count cell colors.

Here is a link that shows different ways to count without VBA

However awoohaw suggestion of using your conditional formatting formula or dates you use to determine the current overdue, etc. is probably the best way to have it automated.

 

[youngxvato]

You can't count cells by their formatting.
What is the conditional formatting rule for the column?

if the formatting rule is something like:
DueDate < CurrentDate

Assuming the two columns below are same length (or one is just cell length of 1):
then your counting rule would be:
=SUM(--(DueDateRange < CurrentDate))

Thanks for the reply! Attached is the conditional formatting for the column in question. However it's formatted now, i get an automated count for the cells that fall under the ="" rule. Ultimately, what i want to happen is to also get a count of the cells that fall under the < NOW()-365 rule.

Please forgive me, I know next to nothing about excel.

 

[AhoyNC]

You will need to change the ranges to match your data (range that holds your dates), but try this.

Book1
	A	B
1	Date
2	4/6/2023
3	6/1/2023
4	7/1/2022
5
6	10/22/2023
7
8		Count
9		2
Sheet2

Cell Formulas
Range	Formula
B9	B9	=ROWS(FILTER($A$2:$A$6,($A$2:$A$6<NOW()-365)+($A$2:$A$6="")))

 

[youngxvato]

UPDATE:

After doing further research I have found the solution to my problem.
The answer I was looking for was to insert the following function in cell c74:
=COUNTIF(c3:c68,"<="&TODAY()-365)

I appreciate all of your responses.

 

[AhoyNC]

Glad you found an answer. I misunderstood and thought you also wanted to count the blank cells.