What's Google Sheets alternative to CountIFS that works with OR logic?-CodePudding

I have blood pressure data in two columns (SYS & DIA) and want to count the states according to these rules:

critical      SYS > 180   OR  DIA > 120 
high stage 2  SYS > 140   OR  DIA > 90  
high stage 1  SYS 130-139 OR  DIA 80-89 
elevated      SYS 120-129 AND DIA < 80  
normal        SYS < 120   AND DIA < 80

so that for

SYS DIA
120 73 (that's elevated)
123 81 (high stage 1)
112 83 (high stage 1)
129 68 (elevated) 
118 72 (normal)
119 80 (elevated)

The result should be

normal: 1
elevated: 2
high stage 1: 3

For "elevated": =COUNTIFS(SYS;">=120";SYS;"<=129";DIA;"<80") seems to work fine.

How do I handle "high stage 1" and other cases with OR???

I'm also considering working around the issue by adding a third column (as shown above) with a description of state via something like:

=IFS(OR(ISBETWEEN(SYS;130;139);ISBETWEEN(DIA;80;89));"High stage 1";AND(ISBETWEEN(SYS;120;129);DIA<80);"Elevated") and so on...

and then I guess I can just count words in that column. Still, one formula for all these states will get kind of messy and I suppose I'm missing a cleaner solution.

CodePudding user response：

see:

=INDEX(QUERY(IF(B11:B="",,
 IF(((A11:A>=120)*(A11:A<=129))*(B11:B<80), A4,
 IF((A11:A<120)*(B11:B<80), A5, 
 IF((A11:A>180) (B11:B>120), A1, 
 IF((A11:A>140) (B11:B>90), A2, 
 IF(((A11:A>=130)*(A11:A<=139)) ((B11:B>=80)*(B11:B<=89)), A3)))))), 
 "select Col1,count(Col1) where Col1 is not null group by Col1 label count(Col1)''"))

CodePudding user response：

You can have a reference (1 on the screenshot) to lookup conditions by SYS or DIA values (presuming only whole numbers are used):

SYS   DIA   Condition
  0     0   normal
120    80   elevated
130    80   high stage 1
141    91   high stage 2
181   121   critical

Then using this reference a formula in one cell (2 on the screenshot):

=QUERY(
  FILTER(
    VLOOKUP(
      QUERY(
        SPLIT(
          FLATTEN(
            ROW(A9:A) & "♥" & {IFNA(MATCH(A9:A, H2:H6)), IFNA(MATCH(B9:B, I2:I6))}
          ),
          "♥"
        ),
        "SELECT MAX(Col2)
         GROUP BY Col1
         LABEL MAX(Col2) ''",
      ),
      {SEQUENCE(ROWS(J2:J6)), J2:J6},
      2
    ),
    A9:A <> "",
    B9:B <> ""
  ),
  "SELECT Col1, COUNT(Col1) GROUP BY Col1 LABEL COUNT(Col1) ''",
)

If you need to get condition for every row (3 on the screenshot) you can use this formula:

=ARRAYFORMULA(
  IFS(
    A9:A = "",,
    B9:B = "",,
    True,
      VLOOKUP(
        QUERY(
          SPLIT(
            FLATTEN(
              ROW(A9:A) & "♥" & {IFNA(MATCH(A9:A, H2:H6)), IFNA(MATCH(B9:B, I2:I6))}
            ),
            "♥"
          ),
          "SELECT MAX(Col2)
           GROUP BY Col1
           LABEL MAX(Col2) ''",
        ),
        {SEQUENCE(ROWS(J2:J6)), J2:J6},
        2
      )
  )
)