google-sheetsgoogle-sheets-formula

Using Column Headers with COUNTIF To Add those that Fall Between Dates in Cells

Here's what I'm trying to do: enter image description here

I have dates as headers. They represent the classes people can attend, with a simple Yes or No in the respective column if they attended.

I am trying to come up with a formula in cell D2 that will give me a count of those classes they attended that fall after the dates in column A but before the ones in column B.

Column C is what the expected outcome should be. Column D is what my attempt was.

Here is the sample sheet (Question 2).

I would like to have one formula that populates the column entirely, rather than having to copy down the formula every time I need to add a row. It would also be nice for it to look past column N as I anticipate adding more classes in the future.

Clarification: The issue I'm seeing here is that it will only look at the dates in row 2 for columns A and B. Looks like the criteria for the filter function is not dynamic. It applies A2 and B2 to all rows.

Solution

  • Restructure the Array First

    Use the following formula:

    =BYROW(ARRAYFORMULA(IF(Arrayformula(IF((E1:N1>=A2:A9)*(E1:N1<=B2:B9),INDEX(E2:N9,0)))="Yes",1,0)), LAMBDA(x, SUM(x)))

    This should result to:

    enter image description here

    Details:

    The restructuring occurs in:

    ARRAYFORMULA(IF(Arrayformula(IF((E1:N1>=A2:A9)*(E1:N1<=B2:B9),INDEX(E2:N9,0)))="Yes",1,0))

    Where it transforms the data in E2:N9 into ones and zeroes based on the dates condition and Yes/No condition. The restructured data should look like this:

    2nd image

    After which, I applied the formula:

    =BYROW(<array range>; LAMBDA(x; SUM(x)))

    from this post's answer by player0.

    NOTE: If the data expands:

    Update:

    I managed to use filter() for the expansion of rows. Here is the updated formula:

    =BYROW(FILTER(ARRAYFORMULA(IF(Arrayformula(IF((E1:N1>=A2:A)*(E1:N1<=B2:B),INDEX(E2:N,0)))="Yes",1,0)),A2:A<>""), LAMBDA(x, SUM(x)))

    Manually editing the ranges E2:N9 and E1:N1 is necessary for column expansion.

    References: