As you know, FREQUENCY calculates the number of times values occur within a range of values, and returns a vertical array of numbers. And, as you'll also know, FREQUENCY has two arguments, data_array and bins_array.
In our formula, the IF() function within FREQUENCY returns the array of values used as the data_array, and ROW()-ROW()+1 returns the array of values used as the bins_array.
Now, let's assume that B13:B4000 and K13:K4000 contain the following data...
A | | | | | | | | | 11/08/15 |
B | | | | | | | | | 11/12/15 |
C | | | | | | | | | 11/09/15 |
B | | | | | | | | | 11/12/15 |
D | | | | | | | | | 11/12/15 |
etc | | | | | | | | | etc |
<tbody>
</tbody>
For the data_array, here's how the IF() function is evaluated...
Code:
IF(K13:K4000=TODAY(),IF(LEN(B13:B4000)>0,MATCH("~"&B13:B4000,B13:B4000&"",0)))
IF({FALSE;TRUE;FALSE;TRUE;TRUE; ... },IF({TRUE;TRUE;TRUE;TRUE;TRUE; ... },{1;2;3;2;5}))
{FALSE;2;FALSE;2;5; ... }
So here we have MATCH return the relative position of each value in B13:B4000 within the range B13:B4000, where the cell in B13:B4000 isn't blank, and where the corresponding value in K13:K4000 is equal to today's date. Otherwise FALSE is returned.
Note that MATCH considers the asterisk (*) and question mark (?) wildcards. So "~" is prepended to each lookup value in B13:B4000. It's an escape character and ensures that the first character of the cell is treated as a regular character instead of a wildcard, in case the first character is an asterisk or question mark. In doing so, though, it converts each value into a text value, if not already so. Hence we append "" to each value in the lookup array B13:B4000 to ensure each one is also a text value.
For the bins_array, here's how ROW()-ROW()+1 is evaluated...
Code:
ROW(B13:B4000)-ROW(B13)+1)
{13;14;15;16;17; ... }-{13}+1
{0;1;2;3;4; ... }+1
{1;2;3;4;5; ... }
And so here's how the rest of the formula is evaluated...
Code:
=SUM(IF(FREQUENCY({FALSE;2;FALSE;2;5; ... },{1;2;3;4;5; ... })>0,1))
=SUM(IF({0;2;0;0;1; ... }>0,1))
=SUM({FALSE;1;FALSE;FALSE;1; ... })
...and then the array of values is passed to the SUM function, which returns the sum.
Hope this helps!