4

I have two sheets, one is a dump sheet of values like below: enter image description here

and the second sheet is using values from this to the total. enter image description here

I am trying to get the total count for the corresponding zone and test name but only if the clash status column says "New" or "Active".

I've tried multiple ways but I am not getting the syntax correct, can anyone help?

  • Show us what you've tried so far. We might be able to suggest a small change that allows this to work. Seeing your work will also give us a better idea of your goal and the method you have in mind. – Folk Jul 26 '17 at 16:50
9

countifs

One way is to use countifs(..., D:D, "New") + countifs(..., D:D, "Active") -- addition works as long as the options in OR are mutually exclusive.

filter

More generally, the combination counta(filter(...)), which is more powerful than countifs, can express OR logic by addition:

=counta(filter(A:A, A:A = "name", B:B = "zone", (D:D = "New") + (D:D = "Active"))

query

Yet more generally, query can return such results for all names and zones at once:

=query(A:D, "select A, B, count(D) where D = 'New' or D = 'Active' group by A, B', 1)

Further reading: filter, query.

| improve this answer | |
  • 1
    user135384's answer about counta(filter(...)) was very helpful to me, but I needed to modify it to counta(iferror(filter(...))) for it to work for me. The issue is that filter(...) returns an error if it finds nothing, and counta(...) then counts that error and returns 1. Wrapping filter(...) with iferror(...) changes any errors filter(...) returns into blanks, which ensures that counta(...) returns 0 when filter(...) finds nothing. – noahtreb Dec 9 '18 at 6:02
1
  • COUNTIF with OR could be solved like this:

    =ARRAYFORMULA(SUM(N(REGEXMATCH(A1:A, "new|active"))))

    0


  • finding for one value:

    =ARRAYFORMULA(COUNTA(IFERROR(FILTER(A1:A, A1:A="Zone A", 
     REGEXMATCH(B1:B, "new|active")))))

    0


  • finding for all values:

    =QUERY(A:B, "select A,count(B) 
             where B matches 'active|new' 
             group by A 
             label count(B)''", 0)

    0

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.