Google Sheets - IF Statement Formula

Question

I need to write a formula that enters 'Y' if the patient is up to date with their immunizations based on their record and age, and 'N' if they are not. Age (D$) is in months and I have a column for every immunization (8 weeks(E$), 12 weeks(F$), 16 weeks(G$), 1 year(H$), 3 years and 4 months(I$)) which is marked as either ("Y" or colored Grey) as well as a final column which the formula is being entered into(J$). Any ideas what formula I need to use?

So far I have been thinking to use an IFS statement { =IFS(D$>=2, AND(E$="Y"),"Y")}. Although I can see the limitations to using this.

question from:https://stackoverflow.com/questions/65922747/google-sheets-if-statement-formula

Answer 1

For every immunization, the patient is not up to date if the corresponding cell is blank and their age is older than the immunization date. You want to check this for multiple immunizations. You can use COUNTIFS to evaluate two (or more) conditions.

=IF(COUNTIFS(E3:I3,"",E$1:I$1,"<="&D3)>0,"N","Y")

Explanation: COUNTIFS returns the number of shots for which the patient is not up to date. IF returns N if the previous value is greater than 0, Y otherwise.

Note that I'm assuming the information on the number of months corresponding to each immunization (E$1:I$1 in the formula above) is present somewhere in the sheet or that you can add it. If this is not the case, you would indeed need to use IFS or something similar.