Formulas and Functions
இந்தக் கட்டுப்பாட்டை மாற்றினால் இந்தப் பக்கம் தானாக மாற்றப்படும்

Functions that accept conditions and wildcards as arguments

Some functions, such as SUM, operate on entire collections. Other functions, such as SUMIF, operate only on the cells in the collection that meet a condition. For example, to add up all the numbers in column B that are less than 5, you could use:

=SUMIF(B,"<5")

The second argument of SUMIF is called a condition because it causes the function to ignore cells that do not meet the requirements.

There are two types of functions that take conditions:

  • Functions that have names ending in IF or IFS. These functions can do numeric comparisons using the comparison operators in their conditions, such as ">5", "<=7" or "<>2".  These functions also accept wildcards in specifying conditions. For example, to count the number of cells in column B that begin with the letter "a", you could use:

    =COUNTIF(B,"a*")

    Note: The function IF is different because it does not take a condition. It instead takes an expression that should evaluate to either TRUE or FALSE.

  • Functions that take conditions, such as HLOOKUP, but can’t do numeric comparisons. These functions sometimes permit the use of wildcards.

The table below lists all the functions that can accept conditions — either numeric comparisons, wildcards, regular expressions, or a combination of the three.

Function

Allows numeric comparisons

Accepts wildcards

Accepts regular expressions

AVERAGEIF

Yes

Yes

Yes

AVERAGEIFS

Yes

Yes

Yes

COUNTIF

Yes

Yes

Yes

COUNTIFS

Yes

Yes

Yes

COUNTMATCHES

No

Yes

Yes

HLOOKUP

No

If exact match specified

Yes

IF

Yes

No

Yes

IFS

Yes

No

Yes

LOOKUP

No

Yes

Yes

MATCH

No

If exact match specified

Yes

MAXIFS

Yes

Yes

Yes

MINIFS

Yes

Yes

Yes

SEARCH

No

Yes

Yes

SUBSTITUTE

No

Yes

Yes

SUMIF

Yes

Yes

Yes

SUMIFS

Yes

Yes

Yes

SWITCH

No

Yes

Yes

TEXTAFTER

No

Yes

Yes

TEXTBEFORE

No

Yes

Yes

TEXTBETWEEN

No

Yes

Yes

VLOOKUP

No

If exact match specified

Yes

XLOOKUP

No

If exact match specified

Yes

XMATCH

No

If exact match specified

Yes

See alsoFunctions overviewList of functions by categoryTypes of arguments and valuesUse string operators and wildcardsTips for selecting financial functionsFunctions used to round valuesReview formula errors and follow best practices
Morty Proxy This is a proxified and sanitized view of the page, visit original site.
உதவியாக இருந்ததா?
எழுத்தின் அளவு: 250
அதிகபட்ச எழுத்தின் அளவு: 250
உங்கள் கருத்திற்கு நன்றி.
Morty Proxy This is a proxified and sanitized view of the page, visit original site.