COUNTIF Function for Dates not working

Good Morning!

I am trying to create a summary tab on my sheet which shows the total number of expired dates in a given range.

Here is a little background of the project I am trying to undertake so you have some context:

I have a tab with certificate expiry dates on for various certificates in different columns which are not together (I.E Column A, then column D, then column H)

I am trying to create a summary tab to show how many expired certificates we have overall

The formula I am trying to use is:

=COUNTIF('Field Staff - A4L'!D4,'Field Staff - A4L'!D4:D20,'Field Staff - A4L'!G4:G20,'Field Staff - A4L'!J4:J21,'Field Staff - A4L'!M4:M20,'Field Staff - A4L'!P4:P20,'Field Staff - A4L'!S4:S20,'Field Staff - A4L'!V4:V20,'Field Staff - A4L'!Y5:Y20,'Field Staff - A4L'!AB4:AB20,"<="&TODAY())

My range is actually quite a bit longer than that, but I have shortened it for the purpose of sharing it here.

I am not even getting an error returned, when I hit enter the formula just stays like it is incomplete.

Please tell me where I am going wrong! Or any suggestions on a different method I could use would be much appreciated.

Many Thanks in advance!


EDIT 12:57 - 29/02/2024

I have attached a sample workbook with the formula provided below used[ATTACH]861301

countif - wont work for more than 1 range
countifs()
needed
BUT if a lot of different columns
then maybe need a countifs()+countifs()

what version of excel do you have
MS-Off Ver
2312
dont follow that - is that the actual version
2007, 2010, 2013, 2016,2019, 2021 , 365 versions ???

as a guess

I have a tab with certificate expiry dates on for various certificates in different columns which are not together (I.E Column A, then column D, then column H)

=countif('Field Staff - A4L'!AB4:AB20,"<="&TODAY()) + countif('Field Staff - A4L'!A4:A20,"<="&TODAY()) + countif('Field Staff - A4L'!D4:D20,"<="&TODAY()) + countif('Field Staff - A4L'!H4:H20,"<="&TODAY())

not sure why AB , as you only mention A,D,H columns

anyway .....

A sample sheet would help here, and possibly will enable a quicker and more accurate solution for you.


The forum does allow for spreadsheets to be uploaded direct to the forum -

Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Here are the instructions, found at the top of the page again

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

If you have a common header on columns D,G etc then you can use

Formula:

Please Login or Register  to view this content.

so above assumes header of "MyHeader" in row 3.

Hi,

Thanks for your help!

I have attached a sample workbook, the formula seemed to *half* work! I have included some comments on the workbook.

In regards to your comments of 'not sure why AB , as you only mention A,D,H columns. anyway …..' As explained, I had quite a bit of data so the formula I pasted would've been exceptionally long so I deleted half the formula for ease of viewing here. A,D,H Columns were just used as an example again for ease of viewing here, in reality I actually have around 20 columns I want to pull from which would've made a very long and unnecessary list!

ok, before looking at the sample
did you see johntopley suggestion
do you have headers as he suggested ?

i'm sure this can be simplied with a LET and using rows() or cols() somehow instead of the multiple use of choosecols

anyway
=COUNTA(FILTER(VSTACK(CHOOSECOLS(B2:P10,3),CHOOSECOLS(B2:P10,6),CHOOSECOLS(B2:P10,9),CHOOSECOLS(B2:P10,12),CHOOSECOLS(B2:P10,15)),VSTACK(CHOOSECOLS(B2:P10,3),CHOOSECOLS(B2:P10,6),CHOOSECOLS(B2:P10,9),CHOOSECOLS(B2:P10,12),CHOOSECOLS(B2:P10,15))<TODAY()))

works on the same sheet - just need to add sheet name

watch for an edit
obviously need to extend the
CHOOSECOLS(B2:P10,3),
to the other 20+ blocks
But as its every 3 , sure its easier with a formula

EDIT
johntopley - replied with the sumproduct example working
I will not do anymore now with the very complicated formula above

Based on your sample file

Please Login or Register  to view this content.

Or

Formula:

Please Login or Register  to view this content.