COUNTIF in Google Sheets with multiple 'not' or 'else' than conditions for responses from Google Forms

Question

I want to count the number of 'other' responses from 2 questions from Google Forms recorded in a Google Sheet.

The 2 questions from Google Forms are for selecting between multiple answers or writing down in text some 'other' response, for example:

Question 1 is 'location' (Site A, Site B, Site C, Site D, other)
Question 2 is 'type of equipment' (PC 1, PC 2, Laptop 1, Laptop 2, other)

So I need to count all the 'other' type of equipment registered that are at 'other' locations.

I've tried different ways to write down the formula (countif, counta, query, etc) but the results give me a 1 when it should give me a zero. I also tried to simplify the formula to write the 'other' types of equipment for 'Site A' but the answer is still 1 weirdly enough.

I'm parting from the answers in : Countifs in Google Sheets with various 'different than' criteria in same row adds +1 value

Those answered formulas work perfectly for a count of all the 'other' equipment in all the 'other' locations but still gives me a response of '1' for specific locations.

I think the problem is that I'm mixing 2 queries/formulas but I'm not making them work together with an 'and' as I require, or if the value is 0 then it's giving me a 1.

This is my attempt A to isolate 1 location with 'other' types of equipment and it somehow gives a '1' response (I tried the variance with '<>PC1' type:

=COUNTA(QUERY(datos_equipos!$J2:$J,"Site A", datos_equipos!$B$2:$B, 
 "where not B contains 'PC 1' 
    and not B contains 'PC 2'
    and not B contains 'Laptop 1'
    and not B contains 'Laptop 2'
    ", 0))

These are my attempts at counting both 'other' responses'

In Attempt A I tried to make 2 queries:

=COUNTA({QUERY(datos_equipos!$J2:$J, "where J <> 'Site A' and J <> 'Site B' and J <> 'Site C' and J <> 'Site D'")
     & query (datos_equipos!$B$2:$B, "where B <> 'PC 1)' 
     and B <> 'PC 2'
     and B <> 'Laptop 1'
     and B <> 'Laptop 2)'", 0)})

Attempt B is the same that A but with the 'different than' <> inside the '' :

=COUNTA({QUERY(datos_equipos!$J2:$J, "where J '<>Site A' and J '<>Site B' and J '<>Site C' and J '<>Site D'")
    & query (datos_equipos!$B$2:$B, "where B '<>PC 1' 
    and B '<>PC 2'
    and B '<>Laptop 1'
    and B '<>Laptop 2'", 0)})

Attempt C is trying to make a direct count while calling each option and trying to exclude blank cells:

=countifs(datos_equipos!$J2:$J, "<>Site A",
                    datos_equipos!$J2:$J, "<>Site B",
                    datos_equipos!$J2:$J, "<>Site C",
                    datos_equipos!$J2:$J, "<>Site D",
                    datos_equipos!$B$2:$B, "<>PC 1", 
                    datos_equipos!$B$2:$B, "<>PC 2", 
                    datos_equipos!$B$2:$B, "<>Laptop 1",
                    datos_equipos!$B$2:$B, "<>Laptop 2",
                    datos_equipos!$B$2:$B,"<>"
                    )

And lastly, attempt D., In this case, the answer is 2. Here I tried to query each option:

=COUNTA({QUERY(datos_equipos!$J2:$J, "where J '<>Site A'")
              & query (datos_equipos!$J2:$J, "where J '<>Site B'")
              & query (datos_equipos!$J2:$J, "where J '<>Site C'")
              & query (datos_equipos!$J2:$J, "where J '<>Site D'")
              & query (datos_equipos!$B$2:$B, "where B '<>PC 1'" )
              & query (datos_equipos!$B$2:$B, "where B 'PC 2'")
              & query (datos_equipos!$B$2:$B, "where B '<>Laptop 1'")
              & query (datos_equipos!$B$2:$B, "where B '<>Laptop 2'")
              , 0})

In sum:

In Google Sheets, I need the number of 'other' equipment registered at 'other' locations. Both are the fields that the user gives instead of the given selectable answers.

I made a test doc for it. So far, the Attempt D in sheet 'repo_equipos_global' in cells N6-I6 works best. As long as there's data that matches, given that the original comes from a form that should not be a problem. [ Link ] (https://docs.google.com/spreadsheets/d/1hnKw6LjG3Vv6-1Yg60RzzXnsh6uzFKYqyu1D36EA1jQ/edit?usp=sharing)

Sure. I made a test doc for it. So far, the Attempt D in sheet 'repo_equipos_global' in cells N6-I6 works best. As long s there's data that matches, given that the original comes from a form that should not be a problem. [ Link ] ( docs.google.com/spreadsheets/d/… ) — deags

player0 player0 · Accepted Answer · 2019-05-15T19:29:04

cell C8:

=ARRAYFORMULA(COUNTA(IFERROR(QUERY(QUERY(
 LOWER({datos_equipos!B2:B, datos_equipos!J2:J}),
 "where Col2 contains '"&LOWER(C2)&"' 
     or Col2 contains 'site1'", 0),
 "select Col1
  where not Col1 contains 'PC 1' 
    and not Col1 contains 'PC 2'
    and not Col1 contains 'PC 3'
    and not Col1 contains 'Laptop 1'
    and not Col1 contains 'Laptop 2'", 0))))

cell F8:

=ARRAYFORMULA(COUNTA(IFERROR(QUERY(QUERY(
 LOWER({datos_equipos!B2:B, datos_equipos!J2:J}),
 "where not Col2 contains 'site1'
    and not Col2 contains 'site2'
    and not Col2 contains 'site3'
    and not Col2 contains 'site 1'
    and not Col2 contains 'site 2' 
    and not Col2 contains 'site 3'", 0),
 "select Col1
  where not Col1 contains 'PC 1' 
    and not Col1 contains 'PC 2'
    and not Col1 contains 'PC 3'
    and not Col1 contains 'Laptop 1'
    and not Col1 contains 'Laptop 2'", 0))))

cell C3 (if you do not differentiate between site 1 and site1 states):

=COUNTIFS(datos_equipos!$B:$B, $A3, datos_equipos!$J:$J, C$2)+
 COUNTIFS(datos_equipos!$B:$B, $A3, datos_equipos!$J:$J, SUBSTITUTE(C$2, " ", ""))

COUNTIF in Google Sheets with multiple 'not' or 'else' than conditions for responses from Google Forms

1 Answers

demo spreadsheet