Given the reference table below:

Date        Location
11/29/2018  Office A
11/29/2018  Office B
11/27/2018  Office C
11/27/2018  Office A
11/27/2018  Office A
11/24/2018  Office C
11/24/2018  Office C
11/11/2018  Office C
11/11/2018  Office B
11/2/2018   Office C

Is it possible to change the output of the query based on its sum of the count of the location column?

The expected output is the following:

Date        Location    Count
11/29/2018  Office A    1
11/29/2018      -       1
11/27/2018  Office C    1
11/27/2018  Office A    2
11/24/2018  Office C    2
11/11/2018  Office C    1
11/11/2018      -       1
11/2/2018   Office C    1

The expected output converts "Office B" Location into an empty string because the sum of the count is less than 3.


Assuming your raw data were in A1:B, this QUERY should produce what you want:

=ArrayFormula(QUERY({A2:A,IF(COUNTIF(B2:B,B2:B)<3,"-",B2:B)},"Select Col1, Col2, COUNT(Col2) Where Col1 Is Not Null Group By Col1, Col2 Order By Col1 Desc Label Col1 'Date', Col2 'Location'"))

  • That worked! Thanks! Is it possible to put the if and count if outside of the query function? I was combining{} 2 query functions to pull from two different sheets and wondering if that would still work – jnsm1138 Dec 11 '18 at 16:45
  • It's difficult to envision what you mean without seeing your data. However, if you just mean you're trying to get the same information from two sheets stacked into one, the portion in curly brackets would look similar to this (see next comment, noticing the double curly brackets front and back): – Erik Tyler Dec 11 '18 at 20:29
  • {{Sheet1!A2:A,IF(COUNTIF(Sheet1!B2:B,Sheet1!B2:B)<3,"-",Sheet1!B2:B)};{Sheet2!A2:A,IF(COUNTIF(Sheet2!B2:B,Sheet2!B2:B)<3,"-",Sheet2!B2:B)}} – Erik Tyler Dec 11 '18 at 20:31
  • There are other ways, but if you just have two sheets, that's one of the more straightforward approaches. – Erik Tyler Dec 11 '18 at 20:32
  • Tried that but It would need to add the count of the B2 on Sheet 1 and the count of B2 on Sheet 2 and their sum is less than 3. – jnsm1138 Dec 12 '18 at 13:01

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.