The Google Sheets COUNTIFS function is used to count items in ranges with multiple test criteria.
It works like a COUNTIF function, but lets you include more than one conditional test.
Here’s an example with a small real estate example dataset:
How can we count the number of deals for apartments and buyers?
To count them, we use a COUNTIFS function:
=COUNTIFS(B2:B11,"Apartment",C2:C11,"Buyer")There are three rows that are apartments AND buyers, highlighted in yellow.
The Google Sheets COUNTIF function is used to count items in a range that match a given condition.
For example, consider the following dataset of customer sales:
This formula counts how many times “John” is found in the range:
=COUNTIF(A2:A11,"John")This one counts how many times “New York” occurs:
=COUNTIF(B2:B11,"New York")And this one counts how many sales are over $500:
=COUNTIF(C2:C11,">500")This post shares my solution to a problem that I faced myself recently.
I needed the unique list of items from a column containing grouped words, separated by commas.
The data was in this format:
As you can see, it’s a list of my online courses, but many of the rows contain more than one item, separated by commas.
Ultimately, I want to transform the data into this format:
Continue reading How To Get A Unique List Of Items From A Column With Grouped Words
The UNIQUE function in Google Sheets is a hugely useful function that takes a range of data and returns the unique rows and discards the duplicate rows.
Here’s a super simple example to show how it works:
Here, the data from column A is passed into the UNIQUE formula and the unique values are returned.
The OR function in Google Sheets is a logical function that outputs TRUE if at least one of the inputs is TRUE, and FALSE only if all the inputs are FALSE.
It has useful applications in data cleaning where it pairs well with the IF Function and AND Function to categorize and organize data.