0

I have column like this

+------+-------------------------+-------------------------+
| Year |         Status1         |         Status2         |
+------+-------------------------+-------------------------+
|    1 | [Blank]                 | Confirmed on YYYY.MM.DD |
|    2 | Confirmed on YYYY.MM.DD | done on YYYY.MM.DD      |
|    1 | Confirmed on YYYY.MM.DD | Confirmed on YYYY.MM.DD |
|    2 | Attended on YYYY.MM.DD  | Confirmed on YYYY.MM.DD |
|    1 | Attended on YYYY.MM.DD  | done on YYYY.MM.DD      |
|    1 | done on YYYY.MM.DD      | done on YYYY.MM.DD      |
|    2 | done on YYYY.MM.DD      | done on YYYY.MM.DD      |
|    1 | Ready on YYYY.MM.DD     | Confirmed on YYYY.MM.DD |
|    2 | Ready on YYYY.MM.DD     | Confirmed on YYYY.MM.DD |
+------+-------------------------+-------------------------+

I need to count the total number of rows that both Status1 and Status2 are containing "Attended", "Done" or "Ready".

Expected Result is 

+------+-----+
| Year | Sum |
+------+-----+
|    1 |   2 |
|    2 |   1 |
+------+-----+

I tried COUNTIFS and as there is not OR operator, if I brute force, then formula calculating sum of Year 1 becomes

 =
countifs(A:A, "=1",B:B, "=*attended*", C:C, "=*attended*")+
countifs(A:A, "=1",B:B, "=*attended*", C:C, "=*done*")+
countifs(A:A, "=1",B:B, "=*attended*", C:C, "=*ready*")+
countifs(A:A, "=1",B:B, "=*done*", C:C, "=*attended*")+
..........

There will be 9 cases and if later I have another new cases, the formula will be terribly long. Therefore, is there any simple solution to my question?

I read this thread Google Spreadsheets: How do I combine COUNTIF with OR but I am not sure how I can apply DCOUNT or COUNTIFS into this situation.

Many thanks.

Community
  • 1
  • 1
pk028382
  • 95
  • 1
  • 10

1 Answers1

1

you may get the output you expect by using a query, like

=query(ArrayFormula({A2:A,A2:A,regexmatch(B2:C, "Attended|done|Ready")} ), "select Col1, Count(Col2) where Col3 = true and Col4 = true group by Col1 label Count(Col2)''")

Example sheet

Explanation

  1. We create a (virtual) array consisting of
    • the values of Col A in Col 1 and repeated in Col 2 of this virtual table.
    • the booleans (true and false) that are returned from the regexmatch for the values 'Attended', 'done' and 'Ready' on the range B2:C
    • this leaves us with a table with 4 columns.
  2. Then we wrap a query() around it that only returns the rows in which both col 3 and col 4 have 'true' (meaning there was a positive match). Of these rows we only return the first col (with the years), and count the number of rows (per year).

Hope that helps ?

JPV
  • 26,499
  • 4
  • 33
  • 48