Google Sheets Count – Formula to Count All Cells Containing Specific Texts in Google Sheets

google sheets

Here is a link to a sample spreadsheet:

https://docs.google.com/spreadsheets/d/1LcfqPxsnQGEVZKd2imM1WPYRvgbZBiEQ18mHVd3znjE/edit?usp=sharing

What I want to do is count all unique values in column C (ID) and all values in column A (initial email) in the month of November.

This is the best I could come up with:

=COUNTUNIQUE(FILTER(Sample!C2:C,Sample!A2:A="Mon, Nov. 23, 2015 2PM"))

But it would only count one specific date in November so I would have to also count all the other dates and then total them. Is there a way for the formula to count all unique values in column C and count all values in Column A containing "Nov"?
To add to that, I also need to count all values in Column F (Response category), containing unique ID for the month of November.

Best Answer

You can filted by regexmatch instead of equality.

=countunique(filter(Sample!C2:C, regexmatch(Sample!A2:A, "Nov")))

Matching the regular expression "Nov" simply means containing "Nov" somewhere in the string.

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 – How to find the sum of specific rows in Column A if rows in Column B meets a given requirement in Google Sheets

Create a Pivot table (under Data) with C for Rows and A and B (Summarise by SUM) for Values.

Best Answer

Related Solutions

Google-sheets – Advanced lookup + sum

Formulae

Screenshots

Explained

Example

Google-sheets – How to find the sum of specific rows in Column A if rows in Column B meets a given requirement in Google Sheets

Related Topic