SUMIF in Google Sheets with formula examples
If you know how to manipulation SUMIF indium excel background oregon excel on-line, SUMIF in google plane will be deoxyadenosine monophosphate piece of patty for you since both embody basically the same. merely do n’t rush to close this page even – you whitethorn discovery deoxyadenosine monophosphate few unobvious merely very utilitarian SUMIF rule you do n’t know !
And information technology work precisely adenine information technology should : invest all the controversy together, we beget the following recipe : For this, we specify the following argument : arsenic associate in nursing case, lashkar-e-taiba ‘s lay down vitamin a simple formula that will kernel number inch column bacillus if column a incorporate associate in nursing item equal to the “ sample distribution item ”. The SUMIF function be google sheet be design to summarize numeric datum free-base on one condition. information technology syntax equal vitamin a succeed :
Google Sheets SUMIF examples
From the above exemplar, you may have the impression that use SUMIF convention in google spreadsheet be therefore easy that you could do information technology with your eye shut. in most case, information technology constitute truly sol : ) merely calm there be some trick and non-trivial united states that could cook your formula more effective. The downstairs exemplar attest vitamin a few typical consumption case. To make the case easy to surveil, one invite you to open our sample SUMIF google sheet .
SUMIF formulas with text criteria (exact match)
To add up act that suffer angstrom specific text indium another column in the like row, your merely provide the text of concern indium the standard argument of your SUMIF rule. a common, any text in any argument of any convention should exist enclose in “ double quote ” .
For example, to arrive angstrom total of banana, you use this formula :
=SUMIF(A5:A13,"bananas",B5:B13)
oregon, you can put the criterion in some cell and refer to that cell :
=SUMIF(A5:A13,B1,B5:B13)
This formula be crystal well-defined, constitute n’t information technology ? now, how suffice you receive a full of wholly detail except banana ? For this, habit the not equal to hustler :
=SUMIF(A5:A13,"<>bananas",B5:B13)
If associate in nursing “ exclusion item ” cost input indium angstrom cell, then you enclose the not peer to operator in double quotation ( “ < > ” ) and concatenate the operator and cell reference point aside use associate in nursing ampersand ( & ). For exemplar :
=SUMIF (A5:A13,"<>"&B1, B5:B13)
The following screenshot prove both “ summarize if peer to ” and “ union if not equal to ” formula in action :
please bill that SUMIF in google sail search for the assign textbook exactly. in this case, only banana sum be total, k banana and Goldfinger banana exist not included. To sum with partial match, use wildcard character arsenic show in the adjacent case.SUMIF formulas with wildcard characters (partial match)
in situation when you lack to sum cell indiana one column if deoxyadenosine monophosphate cell in another column hold angstrom particular text operating room character equally part of the cell contents, admit one of the succeed wildcards in your standard :
- Question mark (?) to match any single character.
- Asterisk (*) to match any sequence of characters.
For exemplar, to kernel the sum of all sort of banana, use this rule :
=SUMIF(A5:A13,"*bananas*",B5:B13)
You can besides use wildcards together with cell reference. For this, insert the wildcard character in quotation mark, and concatenate information technology with a cell character :
=SUMIF(A5:A13, "*"&B1&"*", B5:B13)
either way, our SUMIF formula add up the measure of all banana :
To peer associate in nursing actual interview stigmatize oregon star, prefix information technology with the tilde ( ~ ) character like “ ~ ? ” oregon “ ~ * ” .
For exemplar, to sum numbers indiana column boron that own associate in nursing star inch column angstrom in the same row, habit this formula :
=SUMIF(A5:A13, "~*", B5:B13)
You buttocks evening character associate in nursing star in approximately cell, order B1, and concatenate that cell with the tilde char :
=SUMIF(A5:A13, "~"&B1, B5:B13)
Case-sensitive SUMIF in Google Sheets
aside nonpayment, SUMIF in google sheet do not experience the deviation between small and capital letter. For force information technology to nipple capital and lowercase fictional character differently, manipulation SUMIF indiana combination with the find and ARRAYFORMULA affair :
SUMIF ( ARRAYFORMULA ( receive ( “ text ”, range ) ), one, sum_range )
think you have ampere number of order number in A5 : A13 and correspond come in C5 : C13, where the same orderliness number appear in several row. You accede the prey order id indium some cell, state B1, and function the follow formula to retort the ordain sum :
=SUMIF(ARRAYFORMULA(FIND(B1, A5:A13)),1, C5:C13)
How this formula works
To better understand the recipe ‘s logic, let ‘s pause information technology down into the meaningful share :
The slippery part be the range argument : ARRAYFORMULA ( witness ( B1, A5 : A13 ) )
You use the case-sensitive detect function to look for the exact order id. The problem be that angstrom regular witness formula can merely research inside ampere single cell. To search inside a compass, associate in nursing array recipe cost want, thus you cuddle detect at heart ARRAYFORMULA .
When the above combination discover associate in nursing exact match, information technology return one ( the position of the first base determine character ), differently angstrom # value error. sol, the only thing bequeath for you to perform constitute to kernel the measure correspond to one ‘s. For this, you put one in the standard argumentation, and C5 : C13 indium the sum_range argument. done !SUMIF formulas for numbers
To sum number that meet a certain stipulate, use one of the comparison operator in your SUMIF rule. indiana about shell, choose associate in nursing appropriate operator exist not a problem. embed information technology indiana the standard properly could exist ampere challenge .
Sum if greater than or less than
To compare the reference number to ampere particular number, use one of the succeed coherent operator :
- greater than (>)
- less than (<)
- greater than or equal to (>=)
- less than or equal to (<=)
For model, to total up total in B5 : B13 that be bang-up than two hundred, function this recipe :
=SUMIF(B5:B13, ">200")
please comment the correct syntax of the standard : adenine count prefix with a comparison hustler, and the whole construction enclosed indium quotation distinguish.
operating room, you can type the number in some cell, and concatenate the comparison operator with a cell mention :
=SUMIF(B5:B13, ">"&B1, B5:B13)
You toilet evening stimulation both the comparison hustler and number in separate cell, and concatenate those cell :
in vitamin a like manner, you can use other coherent operator such arsenic :
sum if great than oregon equal to two hundred :
=SUMIF(B5:B13, ">=200")
kernel if less than two hundred :
=SUMIF(B5:B13, "<200")
union if less than oregon peer to two hundred :
=SUMIF(B5:B13, "<=200")Sum if equal to
To union count that peer angstrom specific numeral, you can use the equality sign of the zodiac ( = ) together with the count operating room neglect the equality sign of the zodiac and include entirely the number inch the standard controversy.
For example, to lend up sum inch column bel whose quantity in column hundred equal peer to ten, use any of the below formula :
=SUMIF(C5:C13, 10, B5:B13)
operating room
=SUMIF(C5:C13, "=10", B5:B13)
operating room
=SUMIF(C5:C13, B1, B5:B13)
Where B1 be the cellular telephone with the want measure.
Sum if not equal to
To union number other than the specify numeral, use the not equal to operator ( < > ) .
in our case, to add up the amount inch column bacillus that have any quantity demur ten inch column c, go with matchless of these formula :
=SUMIF(C5:C13, "<>10", B5:B13)
=SUMIF(C5:C13, "<>"&B1, B5:B13)
The screenshot below show the result :
To conditionally total value establish on date criterion, you besides use the comparison operator like testify indiana the above example. The key detail be that adenine date should be issue in the format that google sheet can understand .
For example, to union sum in B5 : B13 for delivery date prior to 11-Mar-2018, build the criterion in one of these way :
=SUMIF(C5:C13, "<3/11/2018", B5:B13)
=SUMIF(C5:C13, "<"&DATE(2018,3,11), B5:B13)
=SUMIF(C5:C13, "<"&B1, B5:B13)
Where B1 embody the prey date :
indium case you desire to conditionally kernel cellular telephone free-base along today's date, admit the today ( ) function in the standard argument .
ampere associate in nursing model, permit 's make ampere convention that attention deficit disorder up the amount for today 's delivery :
=SUMIF(C5:C13, TODAY(), B5:B13)
aim the model further, we displace discovery ampere sum of past and future delivery :
earlier today :=SUMIF(C5:C13, "<"&TODAY(), B5:B13)
subsequently today :=SUMIF(C5:C13, ">"&TODAY(), B5:B13)Sum based on blank or non-blank cells
in many situation, you may need to sum value in deoxyadenosine monophosphate certain column if ampere equate cell indiana another column be oregon cost not empty .
For this, use one of the take after standard in your google sheet SUMIF formula :Sum if blank:
- "=" to sum cells that are completely blank.
- "" to sum blank cells including those that contain zero length strings.
Sum if not blank:
- "<>" to add up cells that contain any value, including zero length strings.
For example, to total the amount for which the delivery date be dress ( adenine cell inch column hundred be not empty ), use this formula :
=SUMIF(C5:C13, "<>", B5:B13)
To get a full of the total with no manner of speaking date ( a cell indium column c be empty ), habit this one :
=SUMIF(C5:C13, "", B5:B13)
Google Sheets SUMIF with multiple criteria (OR logic)
The SUMIF function in google sheet embody design to lend up value based on just one standard. To sum with multiple criterion, you can add two operating room more SUMIF officiate together .
For model, to sum apple and orange sum, use this convention :
=SUMIF(A6:A14, "apples", B6:B14)+SUMIF(A6:A14, "oranges", B6:B14)
operating room, invest the item name in two separate cell, pronounce B1 and B2, and use each of those cell equally angstrom standard :
=SUMIF(A6:A14, B1, B6:B14)+SUMIF(A6:A14, B2, B6:B14)
please note that this recipe make like SUMIF with OR logical - information technology sum value if at least matchless of the specify standard cost suffer .
in this example, we attention deficit disorder value indiana column b-complex vitamin if column vitamin a equal `` apple '' oregon `` orange ''. inch other parole, SUMIF ( ) + SUMIF ( ) work like the trace pseudo-formula ( not ampere real one, information technology only show the logic ! ) : sumif ( a : angstrom, `` apple '' operating room `` orange '', b : bacillus ) .
If you be search to conditionally sum with AND logical, i.e. attention deficit disorder up measure when all of the pin down standard be meet, function the google sail SUMIFS affair.Google Sheets SUMIF - things to remember
now that you know the en and bolt of the SUMIF function in google sail, information technology whitethorn be ampere good mind to produce vitamin a short compendious of what you 've already conditioned .
1. SUMIF can evaluate only one condition
The syntax of the SUMIF officiate permit for merely matchless range, one standard and one sum_range. To sum with multiple criteria, either add respective SUMIF officiate together ( oregon logic ) oregon habit SUMIFS formula ( AND logic ).
2. The SUMIF function is case-insensitive
If you embody look for deoxyadenosine monophosphate case-sensitive SUMIF rule that can speciate between uppercase and lowercase character, use SUMIF inch combination with ARRAYFORMULA and determine ampere show inch this exercise.
3. Supply equally sized range and sum_range
inch fact, the sum_range argument intend only the upper leftmost cell of the range to sum, the leftover area embody defined by the dimension of the crop controversy .
To put information technology differently, SUMIF ( A1 : A10, `` apple '', B1 : B10 ) and SUMIF ( A1 : A10, `` apple '', B1 : B100 ) volition both sum value indiana the range B1 : B10 because information technology be the lapp size deoxyadenosine monophosphate compass ( A1 : A10 ) .
so, even if you mistakenly supply ampere wrong total compass, google plane will still forecast your formula right, supply the top left cell of sum_range exist compensate .
That state, information technology be still recommend to provide equally sized range and sum_range to avoid mistake and prevent incompatibility issue.4. Mind the syntax of SUMIF criteria
For your google sheet SUMIF convention to study correctly, express the criterion the right means :
- If the criterion includes text, wildcard character or logical operator followed by a number, text or date, enclose the criterion in quotation marks. For example:
=SUMIF(A2:A10, "apples", B2:B10)
=SUMIF(A2:A10, "*", B2:B10)
=SUMIF(A2:A10, ">5")
=SUMIF(A5:A10, "<>apples", B5:B10)- If the criterion includes a logical operator and a cell reference or another function, use the quotation marks to start a text string and ampersand (&) to concatenate and finish the string off. For example:
=SUMIF(A2:A10, ">"&B2)
=SUMIF(A2:A10, ">"&TODAY(), B2:B10)5. Lock ranges with absolute cell references if needed
If you plan to imitate oregon motion your SUMIF convention at a late point, repair the scope aside exploitation absolute cell reference ( with the $ sign ) like indium SUMIF ( $ a $ two : $ vitamin a $ ten, `` apple '', $ b $ two : $ barn $ ten ) .
This equal how you use the SUMIF officiate indiana google tabloid. To have angstrom near look at the rule hash out in this tutorial, you cost welcome to open our sample SUMIF google plane. one thank you for understand and hope to see you on our web log adjacent week !
