excelexcel-formularowdate-range

Simple Count of Rows that contain a date within a given Date Range

I have tried to research an answer but cannot seem to find the right solution. I am looking to simply count the number of rows that are in this sheet that contain dates from any given quarter. For example, Simply count the rows where a student took an exam in the 2nd quarter (October 1 2023 and December 31, 2023) or better yet (>=K4 and <=L4).

I use COUNTIFS but it counts each cell

I use SUMPRODUCT and it gives me a 0 because some of the rows/cells don't have dates in that range.

Any help would be appreciated!

I have tried:

=COUNTIFS(D3:I30, ">= " & J4, D3:I30, "<= " & K4)

but that gives me the total cells, I just need the rows.

=SUMPRODUCT give me either a 0 or an #NA

Solution

  • Try using one of the followings:

    enter image description here

    Use MMULT() function as mentioned by Scott Craner Sir:

    =SUMPRODUCT(--(MMULT(((D3:I30>=K4)*(D3:I30<=L4)),{1;1;1;1;1;1})>0))

    Or If applicable, means if you are presently using MS365 and while writing this formula have enabled the Office Insiders then using ETA LAMBDA i.e. without LAMBDA() construction using BYROW() function:

    =SUM(--(BYROW((D3:I30>=K4)*(D3:I30<=L4)>0,OR)))

    Or, If not enabled then:

    =SUM(--(BYROW((D3:I30>=K4)*(D3:I30<=L4)>0,LAMBDA(x,OR(x)))))