Explanation
First, it’s important to note first that Excel dates are simply large serial numbers . When we check for a date with a formula, we are looking for a specific large number, not text .
This formula is a basic example of using the COUNTIFS function with just one condition. The named range dates is supplied as the first argument, and the date in column E is supplied as the second argument for the condition:
=COUNTIFS(dates,E5)
With the date 13-Jun-2020 in cell E5, the COUNTIFS function returns 1, so the formula then simplifies to:
=1>0
which returns TRUE.
By checking if the result from COUNTIFS is greater than zero, we also handle cases where the count is greater than 1 (i.e. the date we are looking for appears more than once), as in cell E7. Any positive result will cause the formula to return TRUE. When COUNTIFS returns a count of zero, the formula will return FALSE.
With a hardcoded date
The best way to hardcode a date into this formula is to use the DATE function like this:
=COUNTIFS(dates,DATE(2020,6,13))>0
The DATE function ensures that the correct date is passed into COUNTIFS, without requiring Excel to interpret a date in text format.
Check for today’s date
To check for today’s date, use the TODAY function like this:
=COUNTIFS(dates,TODAY())>0
Note: the TODAY function will continually update as time passes.
With IF
You can nest this formula inside the IF function as the logical test. For example, to return a final result of “Yes” or “No”, you can use IF like this:
=IF(COUNTIFS(dates,E5),"Yes","No")
Explanation
The goal is to use a formula to check if all cells in a row are blank or empty and return TRUE or FALSE. One way to solve this problem is with the SUMPRODUCT function, as seen in the worksheet above. Another approach is to use the newer BYROW function. Both methods are described below.
SUMPRODUCT function
The SUMPRODUCT function is a Swiss Army Knife function that appears in all kinds of formulas because it can handle many array operations natively in older versions of Excel. In this case, the formula we are using in cell J5 is:
=SUMPRODUCT(--(C5:H5<>""))=0
Working from the inside out, the expression below is used to check for data in row 5 like this:
C5:H5<>""
The <> operator means “not equal to”, so <>"" means “not empty”. Notice we are excluding column B, which contains times. Because there are six cells in the range C5:H5, the expression returns six results in an array like this:
=SUMPRODUCT(--({FALSE,FALSE,TRUE,FALSE,TRUE,TRUE}))=0
In this array, FALSE corresponds to cells that are not empty and TRUE corresponds to cells that are empty . Next, we use a double negative (–) operation to convert TRUE values to 1 and FALSE values to 0. The result looks like this:
=SUMPRODUCT({0,0,1,0,1,1})=0
SUMPRODUCT then sums the values in the array and returns 3:
=3=0 // returns FALSE
The final result in J5 is FALSE since 3 is not equal to zero. This formula will return TRUE only when all cells in a given row are empty. For example, in row 7, which is empty, the formula evaluates like this:
=SUMPRODUCT(--(C7:H7<>""))=0
=SUMPRODUCT(--({FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}))=0
=SUMPRODUCT({0,0,0,0,0,0})=0
=TRUE
See Boolean operations in array formulas for more information about the logic used in SUMPRODUCT.
BYROW function
In newer versions of Excel, you can use the BYROW function to test all rows in a range in one step. The purpose of BYROW is to process data in an array or range in a “by row” fashion. BYROW applies a custom LAMBDA function to each row in a range or array and returns one result per row as a single array . You can see the result in the worksheet below:
The formula used in cell J5 is:
=BYROW(C5:H16,LAMBDA(row,SUM(--(row<>""))=0))
The BYROW function delivers the range C5:H16 one row at a time to the LAMBDA function, which uses the SUM function and the same Boolean logic explained above to count non-blank cells. The result from SUM is then compared to zero to force a TRUE or FALSE result. After processing the entire range, BYROW delivers all results in a single array:
{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}
Notice the array contains 12 results because the original range contains 12 rows. These results spill into the range J5:J16. Other than convenience, the advantage of using BYROW like this is that the results can be used in other functions. For example, you can use BYROW inside the FILTER function to remove blank rows.
