Google Sheets – Count Unique Items in QUERY

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryvlookup

=query(A:B,"Select A, Count(B) where A is not null and B='A' group by A,1")

Best Answer

={"Data", "Count Amount"; 
 ARRAYFORMULA(QUERY({UNIQUE(A3:A), 
 IFERROR(VLOOKUP(UNIQUE(A3:A), 
 QUERY(A:B, "select A,count(B) 
             where A is not null 
               and B='A' 
             group by A 
             label count(B)''", 0), 2, 0), 0)},
 "where Col1 is not null"))}

Related Solutions

Google Sheets Formulas – How to Query for Count of Dates

You need to use 2 formulas.

Create a month column and use the following formula to "display" the month and year:

=text(B1,"mmmm YYYY")

I assumed that the data was in column B, you will need to adjust accordingly. Copy this formula down the entire column

Create a column to hold the labels for the calculation.
Put the value for each month in the cells (January 2011, February 2011...)
Create another column to hold the results, you will need to put the following formula into the cell next to the January 2011 label:

=countif(C:C,D1)

I assumed that the labels are in column D and the results of the text formula are in column C, you will have to adjust accordingly. Copy this formula into the rest of the cells next to the labels.

countif() is a very useful formula. The key to this was the use of the text command to generate a result just showing month and year.

Google-sheets – query all dates within the date range

Might be easier to add a column C with a formula such as:

=if(and(B1>eomonth(Sheet2!$A$2,-1),B1<=eomonth(Sheet2!$A$2,0)),"s",)

and apply a simplified query such as:

=query('Calculated Pay [view]'!B1:C1000,"Select B where C ='s' ")

Best Answer

Related Solutions

Google Sheets Formulas – How to Query for Count of Dates

Google-sheets – query all dates within the date range

Related Topic