When using some functions such as COUNTIF, SUMIF, COUNTIFS, SUMIFS… we see that in these functions, the Criteria parameter appears. So what is Criteria? How to write Criteria correctly? Let’s learn Excel Online to find the answers to those questions right away.
Watching: What is Criteria?
What is Criteria?
Criteria means Condition. Criteria often appear in conditional calculation functions:
COUNTIF function counts by 1 condition COUNTIFS function counts by multiple conditions SUMIF function sums by 1 condition SUMIFS function sums by multiple conditions
How to write the correct condition
To get the functions to do what you want, you need to write the right conditions. But this is more complicated than you think. Because in Excel, data is divided into 3 types: Number data (numerical format), Date data (date and time format), Text data (character string). For each data type, the way to write the condition is different. Detail:
How to write conditions in Number form
When writing directly, you can use operator signs (the signs are greater than, less than, equal to, other …) to write immediately with the condition value, then enclose it all in double quotesWhen writing the reference form , the operator sign will be enclosed in double quotes. Then concatenate the reference with the ampersand &
Examples are as follows:
To count the number of times the sale is greater than 50, we have:
Condition container is the area QuantityCondition count is greater than 50
In this case, we use the COUNTIF function as follows:
Write directly: =COUNTIF(B2:B8,”>50″) where condition is “>50” Write reference: =COUNTIF(B2:B8,”>”&A12) where condition is “>”&A12 is a reference to cell A12
Both ways of writing are correct.
How to write a condition in the form of Date
When writing conditions related to date and time, you need to note that Excel does not allow writing directly in the usual way.
You can only write in 2 ways:
Method 1: Convert the time value to General form (the default form of the time value, which is a number), then write it like Number Method 2: Write as a reference, you can write directly to the cell containing time data.
When you want to write a condition for a Month or Year element while the condition container only has Date data, you need to separate the Month from the Beginning of the month and the End of the month to calculate it.
See also: Download Super Game – Super Game For Android
Examples are as follows:
To calculate the number of sales in January while having only the Date of Sale column, we need to generate 2 values: First Day of January and Last Day of January.
Then use the SUMIFS function as follows:
In cell B11, you enter the first day of the month In cell B12, you enter the last day of the month The formula in cell E11 is:
=SUMIFS(C2:C8,B2:B8,”>=”&B11,B2:B8,”C2:C8 is the range to sum, column QuantityB2:B8 is the first condition range, which is the condition on Sale Date “>=”&B11 is a reference to the value in cell B11, greater than or equal to the first day of the month B2:B8 is the second condition range, which is the Sale Date condition.”
How to write conditions in Text
When writing conditions in Text, there is a feature of Text data that is not accompanied by operator signs. Instead, there are characters representing the starting and ending properties, containing the character …
The * wildcard character has any character, unlimited number of characters. represents any 1 character
Note the order in which the * ? before or after the conditional character. Because this greatly affects the outcome of the condition.
Take a look at the following example:
When we want to calculate the total for the products that are textbooks, we see that the condition to be calculated is that the product name will start with the text of the textbook.
So when we write the condition in the SUMIF function, we will write “SGK*” to represent this meaning.
See also: How to top up Teen Teen Game, Di Dong Online Game, Mien Phi Game: Game Tenten
So through this article, we can clearly understand how to write conditions in Excel’s conditional calculation functions. These are all important functions and are used a lot in daily work.