I'm looking for a formula to run a COUNTIF (or similar) on a range of cells, and where the contained value is a date, to increment the counter - essentially something like:
=COUNTIF(range, if_date())
What I haven't been able to find is a logical test for the if_date() part of the question. Is there a way to test a cell to check whether its content is a date?
This is difficult with worksheet functions because dates in excel are simply formatted numbers - only CELL function lets you investigate the format of a cell (and you can't apply that to a range, so a helper column would be required).......or, if you only have dates and blanks.....or dates and text then it would be sufficient to use COUNT function, i.e.
=COUNT(range)
That counts numbers so it won't be adequate if you want to distinguish dates from numbers. If you do then the number range could be utilised, e.g. if you have numbers in a range and dates but the numbers will all be lower than 10,000 and the dates will all be relatively recent then you could use this version to exclude the numbers
=COUNTIF(range,">10000")
Here's one approach. Using a combination of the answers above do the following:
As a formula, just use the example below with <> replaced with your range reference.
=SUM(IF(ISERROR(DATEVALUE(TEXT(<<RANGE HERE>>, "MM/dd/yyyy"))), 0, 1))
You must enter this as an array formula with CTRL + SHIFT + ENTER.
This assumes that the column of potential date values is in column A. You could do something like this in an adjacent column:
Make a nested formula that converts the "date" to its numeric value if it's valid, or an error value to zero if it's not.
Then it converts the valid numeric values to 1's and leaves the zeroes as they are.
Then sum the new column to get the total number of valid dates.
=IF(IFERROR(DATEVALUE(A1),0)>0,1,0)
To count numbers or dates that meet a single test (such as equal to, greater than, less than, greater than or equal to, or less than or equal to), use the COUNTIF function. In Excel 2007 and later, to count numbers or dates that fall within a range (such as greater than 9000 and at the same time less than 22500), you can use the COUNTIFS function. If you are using Excel 2003 or earlier, you can use the SUMPRODUCT function to count the numbers that fall within a range (COUNTIFS was introduced in Excel 2007).
please see more
There is no interactive solution in Excel because some functions are not vector-friendly, like CELL, above quoted. For example, it's possible counting all the numbers whose absolute value is less than 3, because ABS is accepted inside a formula array.
So I've used the following array formula (Ctrl+Shift+Enter after edit with no curly brackets)
={SUM(IF(ABS(F1:F15)<3,1,0))}
If Column F has
F
1 ... 2
2 .... 4
3 .... -2
4 .... 1
5 ..... 5
It counts 3! (-2,2 and 1). In order to show how ABS is array-friendly function let's do a simple test: Select G1:G5, digit =ABS(F1:F5) in the formula bar and press Ctrl+Shift+Enter. It's like someone write Abs(F1:F5)(1), Abs(F1:F5)(2), etc.
F G
1 ... 2 =ABS(F1:F5) => 2
2 .... 4 =ABS(F1:F5) => 4
3 .... -2 =ABS(F1:F5) => 2
4 .... 1 =ABS(F1:F5) => 1
5 ..... 5 =ABS(F1:F5) => 5
Now I put some mixed data, including 2 date values.
F
1 ... Fab-25-2012
2 .... 4
3 .... May-5-2013
4 .... Ball
5 ..... 5
In this case, CELL fails and return 1
={SUM(IF(CELL("format",F1:F15)="D4",1,0))}
It happens because CELL return the format of first cell of the range. (D4 is a m-d-y format)
So the only thing left is programming! A UDF(User defined Function) for formula array must return a variant array:
Function TypeCell(R As Range) As Variant
Dim V() As Variant
Dim Cel As Range
Dim I As Integer
Application.Volatile '// For revaluation in interactive environment
ReDim V(R.Cells.Count - 1) As Variant
I = 0
For Each Cel In R
V(I) = VarType(Cel) '// Output array has the same size of input range.
I = I + 1
Next Cel
TypeCell = V
End Function
Now is easy (the constant VbDate is 7):
=SUM(IF(TypeCell(F1:F5)=7,1,0))
It shows 2. That technique can be used for any shape of cells. I've tested vertical, horizontal and rectangular shapes, since you fill using for each order inside the function.
A bit long winded but it works for me: try this::
=SUM(IF(OR(ISBLANK(AU2), NOT(ISERR(YEAR(AU2)))),0,1)
+IF(OR(ISBLANK(AV2), NOT(ISERR(YEAR(AV2)))),0,1))
first part of if will allow cell to be blank or if there is something in the cell it tries to convert to a year, if there is an error or there is something other than a date result = 1, do the same for each cell and sum the result
Here is how I was able to trick Excel to count expired certifications in a list. I didn't have a set date, or date range, just current date. "TODAY()" doesn't work in these for Excel 2013. It sees it as text or condition, not the date value. So these previous didn't work for me. So the word problem/scenario: How many people are expired in this list?
Use: =IFERROR(D5-TODAY(),0) Where D5 is the date to be interrogated.
Then use: =IF(J5>=1,1,0) Where J5 is the cell where the first equation is producing either a positive or negative number. This set, I have hidden on the side of the visible sheet, then I just sum the total for the number of unexpired members.
