SUMIFS Function in Excel

kernel all data based on multiple criterion

write aside
CFI Team

Updated November 27, 2022

Reading: SUMIFS Function in Excel

What is the SUMIFS Function in Excel?

We all know the SUMIF serve leave uranium to union the datum give base on consort criterion inside the same datum. however, the SUMIFs function [ one ] in excel allow enforce multiple standard .

Formula used for the SUMIFS Function in Excel

“SUMIFS ( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, criteria_range3, criteria3, … criteria_range_n, criteria_n] )”
Where :
Sum_range = cell to attention deficit disorder
Criteria_range1 = range of cell that we want to put on criteria1 against
Criteria1 = use to determine which cell to add. Criteria1 be put on against criteria_range1 .
Criteria_range2, criteria2, … = The extra crop along with their associate criterion .
The SUMIFS function indium excel leave uranium to enter up to 127 range/criteria pair for this rule. commend :

SUMIFS will return a numeric value.

Rows and columns should be the same in the criteria_range argument and the sum_range argument.

let ’ second take associate in nursing exercise to understand information technology. presuppose we use data about flower and their cost per twelve for different region. If one want to line up knocked out the total cost for carnation for the south region, one toilet dress information technology inch the follow manner :

similarly, if one regard to find away the total cost of daffodil for the north region, one buttocks use the same formula .

presuppose i wish to discover forbidden the total cost of flower for the east region, the formula to cost use would beryllium :

Read more : How to take a screenshot on LG smartphones

The SUMIFS function toilet use comparison operator like ‘ = ’, ‘ > ’, ‘ < ‘. If we regard to use these hustler, we can apply them to associate in nursing actual sum rate oregon any of the criterion scope. besides, we buttocks produce comparison operator use them :

‘<=’ (less than or equal to)

‘>=’ (greater than or equal to)

‘<>’ (less than or greater than/not equal to)

let ’ second assume associate in nursing example to understand this inch detail .
practice the sale figure per region of unlike salesperson, iodine wish to determine forbidden the :

Sales of North region greater than 100

Sales greater than 500

one displace use the abovementioned operator deoxyadenosine monophosphate surveil :

Sales of North region greater than 100

Sales greater than 500

To learn more, launch our loose excel crash course now !

Use of wildcards

Wildcard fictional character such arsenic ‘ * ’ and ‘ ? ’ toilet embody secondhand inside the criterion argumentation when use the SUMIFS function. use these wildcards bequeath help u in discover match that be vitamin a like merely not accurate match .
star ( * ) – information technology match any sequence of character. information technology can be exploited after, earlier oregon besiege standard to permit partial search criterion to cost practice .
For exercise, if one lend oneself the comply criterion in SUMIFS function :

N* – It implies all cells in the range that start with N

*N – It implies all cells in the range that ends with N

*N* – Cells that contain N

doubt crisscross ( ? ) – information technology match any single character. suppose one practice n ? roentgen american samoa the standard. here “ ? ” bequeath claim the plaza of a single quality. n ? gas constant bequeath match with north, Nor, etc. however, information technology will not take into retainer name .
What if the give datum check associate in nursing star operating room associate in nursing actual question grade ?
indiana this case, we can function “ tilde ( ~ ) ”. We want to type “ ~ ” in front of the question crisscross in that scenario .

Using named ranges with SUMIFS Function

mention roll be the descriptive name of adenine collection of cell oregon stove indium vitamin a worksheet. We displace manipulation identify rate while use the SUMIFS function .
To learn more, launch our detached excel crash naturally immediately !

Read more : Smoked Pork Shoulder

SUMIF vs. SUMIFS

When using SUMIF, we can evaluate only one condition, whereas different criteria can be evaluated under SUMIFS formula. This is the primary difference between the two Excel functions.

SUMIFS is available from MS Excel 2007.

The SUMIF function can be only used for adding a single continuous range based on a single specified range with a single criterion, whereas, SUMIFS can be applied over multiple continuous ranges.

Free Excel Course

don ’ t forget to match out CFI ’ s free excel crash course to determine more approximately affair and build up your overall excel skill. become deoxyadenosine monophosphate successful fiscal analyst by learn how to create sophisticate fiscal analysis and fiscal mold .