Using ‘<=’ with SUMIF Function in ExcelĪnother Excel function that logical operators are more commonly used with is the SUMIF function. We added the ‘$’ sign before the column letters and row numbers of cell D2 and D3 ($D$2, $D$3) to make them absolute cells, so it doesn’t change when copying the formula to the rest of the cells (C3:C8). If the condition is FALSE, then D3 is added to B2. Here, if the value in B2 is less than or equal to 150, the value in D2 is added to B2, and the result is displayed in C2. If the price of a dress is less than or equal to $150, we need to add a $20 delivery charge to the net price or add a $10 delivery charge to the price. The formula checks, if the value in B2 is less than or equal to ’50’, and returns ‘Fail’ if the condition is TRUE or returns ‘Pass’ if the condition is FALSE.Īnd the same formula is applied to the rest of cells.įor example, let’s say we have a clothes order list with prices.
The passing mark is ’50’ which is used in the logical_test argument. To do that, try this formula: =IF(B2<=50,"Fail","Pass") Let’s assume, you have a list of student mark lists, and you want to check whether each student is passed or failed based on their test score. The syntax for the IF function is: =IF(logical_test,) The Excel IF function evaluates a logical condition (which is made by ‘less than or equal to’ operator) and returns one value if the condition is TRUE, or another value if the condition is FALSE. The ‘<=’ operator can be used within the ‘logic_test’ argument of the IF function to perform logical operations. In excel, logical operators (like <=) are widely used in parameters of Excel functions such as IF, SUMIF, COUNTIF, and many other functions to perform powerful calculations. Now, you would get the correct result: Using ‘Less Than or Equal To’ Operator with Functions To fix this, you need to enclose a date in the DATEVALUE function, like this: =A1<=DATEVALUE("5-12-2020") Because Excel considers the value as a text string.Īlso, here the date part (5-12-2020) in the formula is considered as a mathematical calculation: In the below example, even though A1 is greater than “4-12-2020”, the result is “TRUE”. Sometimes, when you comparing a date value with a cell, Excel may consider the date value as a text string or arithmetic calculation. Because a text is always considered as the largest value when compared to any number or date in Excel. When only a date is entered, its default time is 12:00 AM, which is midnight.