[Google Sheets] How to use the COUNTIFS function.
How to use the countifs function in google sheets.
Have you ever needed to count the number of items that meet multiple specific criteria in a large dataset without having to manually go through each row? Or do you have a dataset that is constantly changing and you need to be able to get an updated count to report on daily or weekly?
Let’s take a look at how to do just that.
We have a fictitious data set of customers that contains their contact information and order history.
If we need to set up a count to keep track of the number of customers we have in a specific state and have ordered more than $10,000 worth of product we can use the COUNTIFS function on the state and total order amount columns. If you just need to count the total number of customers on one criteria you can use the COUNTIF function.
Check out the card on the top right hand corner of the screen for a full explanation on how to use the COUNTIF function.
In cell B1 if we type equals countifs open brackets and then select the entire STATE column by selecting the column label H followed by a comma. Then in double quotation marks type TEXAS followed by another double quote. Then comma and select column L for the last order quantity followed by a comma and then in double quote but greater than equal double quote ampersand - which is the symbol above the number 7 on most keyboards and close bracket. Press enter and it will show that we have 16 customers in Texas with total orders greater than or equal to ten thousand dollars.
We can change the function to reference cell A1 instead of typing out Texas and reference cell A2 instead of typing out ten thousand. This will allow us to change the state name in cell A1 and automatically update the number of customers in that state with orders over then thousand dollars. Now if we change cell A1 to North Carolina, B1 will automatically update to show that we have 10 customers with over ten thousand in orders in North Carolina. If we want to increase our threshold to fifteen thousand, we just have to update the value in cell A2 and we’ll see that there are 8 customers in North Carolina with orders over fifteen thousand dollars.