Count cells containing Dates (greater than 2014) based on Filtered Cells

Try it like this...

=SUMPRODUCT(SUBTOTAL(2,OFFSET(IJ3,ROW(IJ3:IJ999)-ROW(IJ3),0)),--(IJ3:IJ999>DATE(2014,12,31)))

=SUMPRODUCT(SUBTOTAL(2,OFFSET(IJ3,ROW(IJ3:IJ15)-ROW(IJ3),0)),--(IJ3:IJ15>DATE(2014,12,31)))

Since the data may be filtered we have to use the SUBTOTAL function to get the count of dates in the range.

SUBTOTAL(2, the 2 is the argument index number which means to get the count of numbers. In Excel dates are really just numbers formatted to look like dates. So, the SUBTOTAL function gets an individual count of each cell within the referenced range. If the cell is hidden by the filter then that cell counts as 0. If the cell is visible and contains a date then that cell counts as 1.

The range is defined in the OFFSET function. We have to use the OFFSET function to step through the range one cell at a time in order for the SUBTOTAL to work properly.

Let's assume this is our data:

Data Range

	II	IJ
2	Region	Date
3	North	12/22/2014
4	North	1/5/2015
5	North	2/22/2015
6	East	12/1/2014
7	South	11/27/2014
8	East	3/26/2016
9	North	3/17/2015
10	North	12/10/2014
11	East	8/27/2014
12	West	5/2/2015
13	North	1/1/2016
14	East	8/29/2015
15	East	11/1/2015

When the filter is not applied the formula:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(IJ3,ROW(IJ3:IJ15)-ROW(IJ3),0)),--(IJ3:IJ15>DATE(2014,12,31)))

Result = 8

SUBTOTAL returns the count of dates (numbers) in the range IJ3:IJ15.

IJ3 = 1
IJ4 = 1
IJ5 = 1
IJ6 = 1
IJ7 = 1
IJ8 = 1
IJ9 = 1
IJ10 = 1
IJ11 = 1
IJ12 = 1
IJ13 = 1
IJ14 = 1
IJ15 = 1


(IJ3:IJ15>DATE(2014,12,31))

This tests that the dates are greater than 12/31/2014...

12/22/2014 > 12/31/2014 = FALSE
1/5/2015 > 12/31/2014 = TRUE
2/22/2015 > 12/31/2014 = TRUE
12/1/2014 > 12/31/2014 = FALSE
11/27/2014 > 12/31/2014 = FALSE
3/26/2016 > 12/31/2014 = TRUE
3/17/2015 > 12/31/2014 = TRUE
12/10/2014 > 12/31/2014 = FALSE
8/27/2014 > 12/31/2014 = FALSE
5/2/2015 > 12/31/2014 = TRUE
1/1/2016 > 12/31/2014 = TRUE
8/29/2015 > 12/31/2014 = TRUE
11/1/2015 > 12/31/2014 = TRUE

SUMPRODUCT works with numbers so we have to convert the logical TRUE and FALSE to numbers. One way to do that is to use the double unary minus --.

--TRUE = 1
--FALSE = 0

--(12/22/2014 > 12/31/2014 = FALSE) = 0
--(1/5/2015 > 12/31/2014 = TRUE) = 1
--(2/22/2015 > 12/31/2014 = TRUE) = 1
--(12/1/2014 > 12/31/2014 = FALSE) = 0
--(11/27/2014 > 12/31/2014 = FALSE) = 0
--(3/26/2016 > 12/31/2014 = TRUE) = 1
--(3/17/2015 > 12/31/2014 = TRUE) = 1
--(12/10/2014 > 12/31/2014 = FALSE) = 0
--(8/27/2014 > 12/31/2014 = FALSE) = 0
--(5/2/2015 > 12/31/2014 = TRUE) = 1
--(1/1/2016 > 12/31/2014 = TRUE) = 1
--(8/29/2015 > 12/31/2014 = TRUE) = 1
--(11/1/2015 > 12/31/2014 = TRUE) = 1

Now we have these 2 arrays that get multiplied together:

1*0=0
1*1=1
1*1=1
1*0=0
1*0=0
1*1=1
1*1=1
1*0=0
1*0=0
1*1=1
1*1=1
1*1=1
1*1=1

The results are then summed and this is the final result of the formula:

SUMPRODUCT({0;1;1;0;0;1;1;0;0;1;1;1;1}) = 8

When the range is not filtered there are 8 dates greater than 12/31/2014.

Now, if we filter the data on Region = North, we get:

SUBTOTAL(2,OFFSET(IJ3,ROW(IJ3:IJ15)-ROW(IJ3),0))

IJ3 = visible = 1
IJ4 = visible = 1
IJ5 = visible = 1
IJ6 = hidden = 0
IJ7 = hidden = 0
IJ8 = hidden = 0
IJ9 = visible = 1
IJ10 = visible = 1
IJ11 = hidden = 0
IJ12 = hidden = 0
IJ13 = visible = 1
IJ14 = hidden = 0
IJ15 = hidden = 0

--(IJ3:IJ15>DATE(2014,12,31))

IJ3 = 0
IJ4 = 1
IJ5 = 1
IJ6 = 0
IJ7 = 0
IJ8 = 1
IJ9 = 1
IJ10 = 0
IJ11 = 0
IJ12 = 1
IJ13 = 1
IJ14 = 1
IJ15 = 1

The 2 arrays are multiplied together:

1*0=0
1*1=1
1*1=1
0*0=0
0*0=0
0*1=0
1*1=1
1*0=0
0*0=0
0*1=0
1*1=1
0*1=0
0*1=0

The results are then summed and this is the final result of the formula:

SUMPRODUCT({0;1;1;0;0;0;1;0;0;0;1;0;0}) = 4

When filtered on Region = North there are 4 dates greater than 12/31/2014.

=SUMPRODUCT(SUBTOTAL(2,OFFSET(IJ3,ROW(IJ3:IJ15)-ROW(IJ3),0)),--(IJ3:IJ15>DATE(2014,12,31)))

OFFSET(reference, number-of-rows, number-of-columns, height, width)

The height and width arguments are optional and are not needed in this application so they are omitted.

OFFSET(IJ3,ROW(IJ3:IJ999)-ROW(IJ3),0)

ROW(IJ3:IJ999)-ROW(IJ3) generates an array for the number-of-rows argument.

ROW(IJ3)-ROW(IJ3) = 0
ROW(IJ4)-ROW(IJ3) = 1
ROW(IJ5)-ROW(IJ3) = 2
ROW(IJ6)-ROW(IJ3) = 3
ROW(IJ7)-ROW(IJ3) = 4
ROW(IJ8)-ROW(IJ3) = 5
etc
etc
ROW(IJ999)-ROW(IJ3) = 996

This means:

Offset cell IJ3 0 rows = IJ3
Offset cell IJ3 1 row = IJ4
Offset cell IJ3 2 rows = IJ5
Offset cell IJ3 3 rows = IJ6
Offset cell IJ3 4 rows = IJ7
Offset cell IJ3 5 rows = IJ8
Offset cell IJ3 6 rows = IJ9
etc
etc
Offset cell IJ3 996 rows = IJ999

We don't want to offset by any columns so the number-of-columns argument is 0.

Then, the SUBTOTAL function gets the count of dates from each of those cells.

This may make it easier to understand.

If we use SUBTOTAL without the OFFSET:

SUBTOTAL(2,IJ3:IJ999)

Then we get a single result (count) for the entire range.

We need a result (count) for each cell in the range. The OFFSET function allows us to do that.

You're welcome. Thanks for the feedback!