dategoogle-sheetscountlogic

Google Sheets: Formula requires Complex Logic regarding dates

I have a spreadsheet with four data columns, and a reference date:

Spreadsheet Image

I am trying to: Count the number of people who are Members, AND who were younger than 16 on B1, AND who did not die before B1.

For the first two parts of the Formula I have this:

=COUNTIFS(B3:B, TRUE, C3:C, "<"&DATE(YEAR(B1)-16, MONTH(B1), DAY(B1)))

For the third part of the formula I keep getting wrong answers. It feels like it should be simple, but the IF is making it hard:

"IF D3:D exists, and IF D3:D is before B1: do not Count; but IF D3:D exists, and IF( D3:D is blank OR if D3:D is after B1), Count".

Here is a Doodle: https://docs.google.com/spreadsheets/d/1q4qRgXSq6xXhddArPCoBIuOOupRvokUd0andADCNN0Q/edit?gid=64151107#gid=64151107

Solution

  • Here's one approach you may test out:

    =counta(ifna(filter(A3:A,B3:B,C3:C>edate(B1,-12*16),(D3:D="")+(len(D3:D))*(D3:D>B1))))

    enter image description here