Searching worksheets can be done manually with little data. If you have a large table in your worksheet, if you have multiple worksheets or even workbooks connected to each other, this work becomes quite tiring. For example, you have a supermarket dataset.
You wonder how many of 'x' items are sold per day. It would be terrible to count them one by one. You can find a detailed article on how to count under a certain condition here→ ” How to use the COUNTIF function in google sheets? “
In this article, we will explain how we can count under more than one condition.
Where is COUNTIFS used?
We will explain the COUNTIFS usage areas with examples and examine the syntax.
Using COUNTIFS in the text tables
Our table consists of texts. We are wondering about the situations where both “Art” and “Florida” conditions are met. For this:
Come to the cell where you want to print the result and type
=COUNTIFS(A: A, "Art", D:D, "Florida")
- A:A → The range of cells I want to search. (Cell range for condition 1st.) I can specify a specific range, as well as select the column. Just click on column A.
- "Art"→ 1st condition
- D:D→ Cell range for condition 2
- "Florida"→2nd condition
Using COUNTIFS in numeric tables
Our table consists of numerical values. This is a sales high school. We wonder how many items were sold with more than 5 and how many with profits over 200. For this;
Come to the cell where you want to print the result and write
- B:B→ Range of cells I want to search.
- "<5"→ 1st condition. How many products were sold more than 5?
- E:E→Range of cells I want to search.
- ">200"→ 2nd condition. How many products have more than 200 profits?
Using COUNTIFS in date tables
With this example, we look at the number of sales greater than 5 and how many sales were made after 8.06.2015.
- B:B→ Since I am looking for sales numbers greater than 5, I search for column B. That is, the cell range I searched for for the first condition.
- “>5”→ I'm looking for those with sales greater than 5
- C:C→ I am searching on the date column because I entered a date condition. So the range of cells I'm searching for the second condition
- “>08.11.2017” → My second condition. I'm looking at sales after that date.