Google-sheets – COUNTIFS with multiple OR

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryregex

I have two sheets, one is a dump sheet of values like below:

and the second sheet is using values from this to the total.

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?

Best Answer

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)

Formulae

(1) =TRANSPOSE(ARRAY_ROW(JAN!A1,FEB!A1,MAR!A1))
(2) =ARRAYFORMULA(VLOOKUP(A1:A3,VLOOKUP!A1:B3,2,0))
(3) =QUERY(A1:B3, "SELECT A, SUM(B) GROUP BY A LABEL SUM(B) 'Totals'")

Screenshots

(1)
enter image description here

(2)
enter image description here

(3)
enter image description here

Explained

The ARRAY_ROW formula allows for easy preparation of a range. The TRANSPOSE formula re-arranges the row into a column position. This way all entries are nicely presented (1). The VLOOKUP and the ARRAYFORMULA will efficiently look for all corresponding numbers in the VLOOKUP sheet (2). The QUERY formula is used to perform the totals calculation (3).

Example

I've created an example file for you: Google Sheets advanced lookup + sum

Google-sheets – Google spreadsheet lookup

You can use either filter or query for this.

=filter(Sheetname1!B:B, A:A = X)

=query(Sheetname1!A:B, "select B where A = X")