Calculating expired boxes in my matrix

[craigmcgowan08]

Hello,
I need some assistance. I have a training matrix with all my staff and their certification dates. The collum beside the date of certification figures out the date it will expire and turns yellow 90 days before expiry and turns red when expired or if no date has been inputted.
I used the formula =counting(B9:BE45,"<"&today()). This gives me a total of 72 red boxes, but when I count the red boxes, there are over 95. How do I adjust this.

 

[craigmcgowan08]

Hello,
I need some assistance. I have a training matrix with all my staff and their certification dates. The collum beside the date of certification figures out the date it will expire and turns yellow 90 days before expiry and turns red when expired or if no date has been inputted.
I used the formula =counting(B9:BE45,"<"&today()). This gives me a total of 72 red boxes, but when I count the red boxes, there are over 95. How do I adjust this.

Sorry, meant countif. Auto correct changed it to counting.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=COUNTIFS(B9:BE45,"")+COUNTIFS(B9:BE45,"<"&TODAY())

 

[craigmcgowan08]

Hi & welcome to MrExcel.
How about

=COUNTIFS(B9:BE45,"")+COUNTIFS(B9:BE45,"<"&TODAY())

 

[craigmcgowan08]

Thanks so much for responding. I tried that code and it gave me a total of 1789 expired boxes. I should only have about 80 .

 

[Fluff]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.