Sum formulas
Totals with conditions, running totals, totals across columns and ranges that grow. 75 answers, each checked by running it, with a live example and an exercise.
How to sum values based on criteria in Excel (SUMIF)
=SUMIF(B2:B11,G1,D2:D11)Use =SUMIF(criteria_range, criteria, sum_range): it adds the numbers in sum_range on the rows where criteria_range matches.
How to sum with multiple criteria in Excel (SUMIFS)
=SUMIFS(D2:D11,B2:B11,G1,C2:C11,G2)Use SUMIFS with the sum range first, then one range-and-condition pair per criterion: =SUMIFS(sum_range, range1, crit1, range2, crit2).
How to calculate a running total in Excel
=SUM($B$2:B2)Sum from a locked first cell to the current row, =SUM($B$2:B2), and fill it down. The range grows by one row each time.
How to sum values between two dates in Excel
=SUMIFS(D2:D11,A2:A11,">="&G1,A2:A11,"<="&G2)Use SUMIFS with two date conditions on the same column: =SUMIFS(sum_range, dates, ">="&start, dates, "<="&end).
How to sum if a cell contains specific text in Excel
=SUMIF(A2:A9,"*"&E1&"*",B2:B9)Use SUMIF with wildcards around the text: =SUMIF(range,"*"&text&"*",sum_range).
How to sum the top 3 values in Excel
=SUM(LARGE(B2:B8,{1,2,3}))Use LARGE with an array of ranks inside SUM: =SUM(LARGE(range,{1,2,3})).
How to sum a column in Excel
=SUM(B2:B7)Select the cell below or beside the numbers and use
=SUM(B2:B7), or press Alt+= (AutoSum) to have Excel write it for you.How to sum non-adjacent cells in Excel
=SUM(B5,B8,B11)Separate the cells or ranges with commas inside SUM, as in
=SUM(B5,B8,B11); a colon would add everything in between.How to sum a whole column without the header in Google Sheets
=SUM(B2:B)Use an open-ended range that starts below the header:
=SUM(B2:B)adds everything from B2 to the bottom of the sheet, including rows added later.How to sum values greater than a number in Excel
=SUMIF(B2:B9,">"&E1)Use SUMIF with the operator in quotes joined to the cell that holds the number:
=SUMIF(B2:B9,">"&E1).How to sum values between two numbers in Excel
=SUMIFS(B2:B10,B2:B10,">="&E1,B2:B10,"<="&E2)Use SUMIFS with two conditions on the same column:
=SUMIFS(B2:B10,B2:B10,">="&E1,B2:B10,"<="&E2).How to sum if a cell is not blank in Excel
=SUMIFS(B2:B8,C2:C8,"<>")Use "<>" (not equal to nothing) as the condition:
=SUMIFS(B2:B8,C2:C8,"<>")adds the rows where column C has something in it.How to sum if a cell is blank in Excel
=SUMIFS(B2:B8,C2:C8,"")Use an empty string as the condition:
=SUMIFS(B2:B8,C2:C8,"")adds the rows where column C is empty.How to sum with OR criteria in Excel
=SUM(SUMIFS(D2:D11,C2:C11,G1:G2))Give SUMIFS a list of values and add up the results:
=SUM(SUMIFS(D2:D11,C2:C11,G1:G2))totals rows matching either product.How to sum with OR conditions on different columns in Excel
=SUMPRODUCT(((B2:B11=G1)+(C2:C11=G2)>0)*D2:D11)Add the tests and keep rows where the result is above 0:
=SUMPRODUCT(((B2:B11=G1)+(C2:C11=G2)>0)*D2:D11).How to sum by month in Excel
=SUMIFS($C$2:$C$11,$A$2:$A$11,">="&E2,$A$2:$A$11,"<="&EOMONTH(E2,0))List the first day of each month and use SUMIFS from that date to the month end:
=SUMIFS($C$2:$C$11,$A$2:$A$11,">="&E2,$A$2:$A$11,"<="&EOMONTH(E2,0)).How to sum by year in Excel
=SUMIFS($C$2:$C$10,$A$2:$A$10,">="&DATE(E2,1,1),$A$2:$A$10,"<="&DATE(E2,12,31))Use SUMIFS with January 1 and December 31 of the year in E2:
=SUMIFS($C$2:$C$10,$A$2:$A$10,">="&DATE(E2,1,1),$A$2:$A$10,"<="&DATE(E2,12,31)).How to sum by day of the week in Excel
=SUMPRODUCT((TEXT($A$2:$A$13,"ddd")=D2)*$B$2:$B$13)Turn each date into its day name and compare:
=SUMPRODUCT((TEXT($A$2:$A$13,"ddd")=D2)*$B$2:$B$13).How to sum by quarter in Excel
=SUMPRODUCT((YEAR($A$2:$A$12)=$F$1)*(ROUNDUP(MONTH($A$2:$A$12)/3,0)=E3)*$C$2:$C$12)Work out each date's quarter with
ROUNDUP(MONTH(date)/3,0)and add the matching rows:=SUMPRODUCT((YEAR($A$2:$A$12)=$F$1)*(ROUNDUP(MONTH($A$2:$A$12)/3,0)=E3)*$C$2:$C$12).How to sum by week in Excel
=SUMIFS($C$2:$C$11,$A$2:$A$11,">="&E2,$A$2:$A$11,"<"&E2+7)List the first day of each week and add the seven days from there:
=SUMIFS($C$2:$C$11,$A$2:$A$11,">="&E2,$A$2:$A$11,"<"&E2+7).How to calculate a year-to-date (YTD) total in Excel
=SUMIFS(C2:C10,A2:A10,">="&DATE(YEAR(F1),1,1),A2:A10,"<="&F1)Use SUMIFS from January 1 of the as-of date's year to the as-of date:
=SUMIFS(C2:C10,A2:A10,">="&DATE(YEAR(F1),1,1),A2:A10,"<="&F1).How to sum the last 30 days in Excel
=SUMIFS(C2:C9,A2:A9,">"&F1-30,A2:A9,"<="&F1)Use SUMIFS with a window that ends on the as-of date and starts 30 days earlier:
=SUMIFS(C2:C9,A2:A9,">"&F1-30,A2:A9,"<="&F1).How to sum the last N values in a column in Excel
=SUM(TAKE(FILTER(B2:B13,B2:B13<>""),-E1))In Excel 365, keep the filled cells and take the last N:
=SUM(TAKE(FILTER(B2:B13,B2:B13<>""),-E1)).How to sum every nth row in Excel
=SUMPRODUCT((MOD(ROW(B2:B11)-ROW(B2)+1,E1)=0)*B2:B11)Test each row's position with MOD and add the rows where it divides evenly:
=SUMPRODUCT((MOD(ROW(B2:B11)-ROW(B2)+1,E1)=0)*B2:B11).How to sum every other column in Excel
=SUMPRODUCT((MOD(COLUMN(B2:G2)-COLUMN(B2)+1,2)=0)*B2:G2)Test each column's position with MOD:
=SUMPRODUCT((MOD(COLUMN(B2:G2)-COLUMN(B2)+1,2)=0)*B2:G2)adds the 2nd, 4th and 6th columns.How to sum a range that contains errors in Excel
=SUM(IFERROR(C2:C8,0))Turn the errors into 0 before adding with
=SUM(IFERROR(C2:C8,0)), wrapped in ARRAYFORMULA in Google Sheets.How to sum absolute values in Excel
=SUMPRODUCT(ABS(B2:B8))Wrap the range in ABS inside SUMPRODUCT:
=SUMPRODUCT(ABS(B2:B8))adds every value as a positive number.How to sum only positive numbers in Excel
=SUMIF(C2:C9,">0")Use SUMIF with ">0",
=SUMIF(C2:C9,">0"), or with "<0" to add only the negatives.How to sum a column based on its header in Excel
=SUM(XLOOKUP(J1,B1:G1,B2:G6))Look up the column by its header and sum what comes back:
=SUM(XLOOKUP(J1,B1:G1,B2:G6)).How to sum a row based on a lookup value in Excel
=SUM(XLOOKUP(H1,A2:A6,B2:E6))Look up the row and sum the result:
=SUM(XLOOKUP(H1,A2:A6,B2:E6))returns the whole matching row and adds it.How to sum values that match a row label and a column header in Excel
=SUMPRODUCT((B2:B7=I1)*(C1:F1=I2)*C2:F7)Multiply a test on the row labels by a test on the headers:
=SUMPRODUCT((B2:B7=I1)*(C1:F1=I2)*C2:F7).How to SUMIF across multiple columns in Excel
=SUMPRODUCT((B2:B8=I1)*C2:F8)SUMIF adds only one column, so use SUMPRODUCT:
=SUMPRODUCT((B2:B8=H1)*C2:F8)totals every column for the rows that match.How to build a summary table with SUMIFS in Excel
=SUMIFS($D$2:$D$12,$B$2:$B$12,$F2,$A$2:$A$12,">="&G$1,$A$2:$A$12,"<="&EOMONTH(G$1,0))Write one SUMIFS with mixed references and fill it across and down:
=SUMIFS($D$2:$D$12,$B$2:$B$12,$F2,$A$2:$A$12,">="&G$1,$A$2:$A$12,"<="&EOMONTH(G$1,0)).How to sum values that are not in a list in Excel
=SUMPRODUCT((COUNTIF(D2:D3,A2:A10)=0)*B2:B10)Count each row's value in the list and add the rows where the count is 0:
=SUMPRODUCT((COUNTIF(D2:D3,A2:A10)=0)*B2:B10).How to sum if a cell does not contain specific text in Excel
=SUMIF(A2:A9,"<>*"&E1&"*",B2:B9)Put "<>" in front of the wildcard pattern:
=SUMIF(A2:A9,"<>*"&E1&"*",B2:B9).How to sum if a cell contains one of several words in Excel
=SUMPRODUCT(((ISNUMBER(SEARCH(E1,A2:A9))+ISNUMBER(SEARCH(E2,A2:A9)))>0)*B2:B9)Add a SEARCH test per word and keep rows where any is found:
=SUMPRODUCT(((ISNUMBER(SEARCH(E1,A2:A9))+ISNUMBER(SEARCH(E2,A2:A9)))>0)*B2:B9).How to do a case-sensitive SUMIF in Excel
=SUMPRODUCT(EXACT(A2:A8,E1)*B2:B8)SUMIF ignores case, so test with EXACT inside SUMPRODUCT:
=SUMPRODUCT(EXACT(A2:A8,E1)*B2:B8).How to make SUMIFS ignore a criterion when the cell is blank in Excel
=SUMIFS(C2:C11,A2:A11,IF(F1="","*",F1),B2:B11,IF(F2="","*",F2))Swap a blank input for the wildcard "*":
=SUMIFS(C2:C11,A2:A11,IF(F1="","*",F1),B2:B11,IF(F2="","*",F2)).How to sum values without counting duplicates in Excel
=SUMPRODUCT(B2:B10/COUNTIF(A2:A10,A2:A10))Divide each amount by how often its ID appears, then add:
=SUMPRODUCT(B2:B10/COUNTIF(A2:A10,A2:A10)).How to multiply two columns and then sum in Excel
=SUMPRODUCT(B2:B7,C2:C7)Use SUMPRODUCT:
=SUMPRODUCT(B2:B7,C2:C7)multiplies each quantity by its price and adds the results.How to use SUMPRODUCT with criteria in Excel
=SUMPRODUCT((A2:A8=G1)*C2:C8*D2:D8)Multiply a TRUE/FALSE test into the product:
=SUMPRODUCT((A2:A8=G1)*C2:C8*D2:D8).How to calculate a weighted score in Excel
=SUMPRODUCT(B2:E2,$B$8:$E$8)Multiply each score by its weight and add:
=SUMPRODUCT(B2:E2,$B$8:$E$8), filled down for each option.How to calculate a running total by group in Excel
=SUMIFS($C$2:C2,$B$2:B2,B2)Use SUMIFS on ranges that grow as you fill down:
=SUMIFS($C$2:C2,$B$2:B2,B2)adds only the earlier rows of the same group.How to calculate a running balance in Excel
=SUM($C$2:C2)-SUM($D$2:D2)Subtract running withdrawals from running deposits:
=SUM($C$2:C2)-SUM($D$2:D2), filled down.How to keep a running total blank on empty rows in Excel
=IF(B2="","",SUM($B$2:B2))Test the row first:
=IF(B2="","",SUM($B$2:B2))shows the running total only where there is a value.How to sum up to a selected month in Excel
=SUM(TAKE(B2:M2,,MATCH($P$1,$B$1:$M$1,0)))Find the month's position and take that many columns:
=SUM(TAKE(B2:M2,,MATCH($P$1,$B$1:$M$1,0))), filled down.How to sum until the first blank cell in Excel
=SUM(TAKE(B2:B9,XMATCH(TRUE,B2:B9="")-1))Find the first blank and take the rows above it:
=SUM(TAKE(B2:B9,XMATCH(TRUE,B2:B9="")-1)).How to sum values for the most recent date in Excel
=SUMIFS(C2:C9,A2:A9,MAX(A2:A9))Use MAX to find the latest date and SUMIFS to add its rows:
=SUMIFS(C2:C9,A2:A9,MAX(A2:A9)).How to sum values if the date is before a certain date in Excel
=SUMIF(B2:B8,"<"&F1,C2:C8)Use SUMIF with "<" joined to the date cell:
=SUMIF(B2:B8,"<"&F1,C2:C8).How to sum by fiscal year in Excel
=SUMPRODUCT((YEAR(EDATE($A$2:$A$9,6))=E2)*$C$2:$C$9)Shift each date forward by the months before the fiscal year starts, then compare years:
=SUMPRODUCT((YEAR(EDATE($A$2:$A$9,6))=E2)*$C$2:$C$9)for a year starting in July.How to calculate total overtime hours in Excel
=SUMPRODUCT((B2:B8>E1)*(B2:B8-E1))Add up only the hours over the limit:
=SUMPRODUCT((B2:B8>E1)*(B2:B8-E1))with the limit (40) in E1.How to sum if one column is greater than another in Excel
=SUMPRODUCT((C2:C7>B2:B7)*C2:C7)SUMIF can't compare two columns row by row, so use SUMPRODUCT:
=SUMPRODUCT((C2:C7>B2:B7)*C2:C7).How to sum numbers separated by commas in one cell in Excel
=SUM(--TEXTSPLIT(B2,","))Split the text and add the pieces:
=SUM(--TEXTSPLIT(B2,","))in Excel 365, or=SUM(SPLIT(B2,","))in Google Sheets.How to sum cells that contain numbers and text in Excel
=SUMPRODUCT(--SUBSTITUTE(B2:B6," kg",""))Strip the text and convert what is left:
=SUMPRODUCT(--SUBSTITUTE(B2:B6," kg",""))adds "12 kg", "8 kg" and so on.Why is my SUM formula returning 0 or too small a total in Excel
=SUMPRODUCT(--B2:B7)SUM skips numbers stored as text, so convert them while adding with
=SUMPRODUCT(--B2:B7)and then fix the column for good.How to sum each row with one formula in Excel
=B2:B6+C2:C6+D2:D6Add the columns as ranges and let the result spill,
=B2:B6+C2:C6+D2:D6, wrapped in ARRAYFORMULA in Google Sheets.How to look up a value and sum all matches in Excel
=SUMIF(A2:A9,F1,C2:C9)Lookup functions stop at the first match, so use SUMIF:
=SUMIF(A2:A9,F1,C2:C9)adds every row for that ID.How to sum by a category that is stored in another table in Excel
=SUMPRODUCT(SUMIFS(B2:B9,A2:A9,D2:D6)*(E2:E6=H1))Sum each product, then keep the products in the category:
=SUMPRODUCT(SUMIFS(B2:B9,A2:A9,D2:D6)*(E2:E6=H1)).How to multiply quantities by prices from a lookup table in Excel
=SUMPRODUCT(B2:B6,XLOOKUP(A2:A6,D2:D6,E2:E6))Look up every price at once and multiply inside SUMPRODUCT:
=SUMPRODUCT(B2:B6,XLOOKUP(A2:A6,D2:D6,E2:E6)).How to sum the top 3 values with a condition in Excel
=SUM(LARGE(FILTER(C2:C10,B2:B10=F1),{1,2,3}))Filter first, then take the three largest:
=SUM(LARGE(FILTER(C2:C10,B2:B10=F1),{1,2,3})).How to sum scores and drop the lowest in Excel
=SUM(B2:F2)-MIN(B2:F2)Subtract the smallest score from the total:
=SUM(B2:F2)-MIN(B2:F2), filled down for each student.How to calculate the sum of squares in Excel
=SUMSQ(B2:B7)Use SUMSQ:
=SUMSQ(B2:B7)squares each value and adds the squares.How to sum the digits of a number in Excel
=SUMPRODUCT(--MID(A2,SEQUENCE(LEN(A2)),1))Split the number into single characters and add them:
=SUMPRODUCT(--MID(A2,SEQUENCE(LEN(A2)),1)).How to sum if a checkbox is checked in Excel
=SUMIF(C2:C8,TRUE,B2:B8)A checked box is TRUE, so use it as the condition:
=SUMIF(C2:C8,TRUE,B2:B8).How to total each category with one formula in Excel
=HSTACK(UNIQUE(B2:B9),SUMIF(B2:B9,UNIQUE(B2:B9),C2:C9))List the categories with UNIQUE and sum each with SUMIF, side by side:
=HSTACK(UNIQUE(B2:B9),SUMIF(B2:B9,UNIQUE(B2:B9),C2:C9)).How to sum by group with QUERY in Google Sheets
=QUERY(A1:D10,"select A, sum(D) group by A order by sum(D) desc label sum(D) 'Total sales'",1)Use QUERY with group by:
=QUERY(A1:D10,"select A, sum(D) group by A order by sum(D) desc label sum(D) 'Total sales'",1).How to sum values above the average in Excel
=SUMIF(B2:B8,">"&AVERAGE(B2:B8))Build the condition from AVERAGE:
=SUMIF(B2:B8,">"&AVERAGE(B2:B8)).How to calculate last year's year-to-date total in Excel
=SUMIFS(B2:B11,A2:A11,">="&DATE(YEAR(E1)-1,1,1),A2:A11,"<="&EDATE(E1,-12))Move the YTD window back a year:
=SUMIFS(B2:B11,A2:A11,">="&DATE(YEAR(E1)-1,1,1),A2:A11,"<="&EDATE(E1,-12)).How to calculate the net of credits and debits in Excel
=SUMIF(B2:B8,"Credit",C2:C8)-SUMIF(B2:B8,"Debit",C2:C8)Subtract one SUMIF from the other:
=SUMIF(B2:B8,"Credit",C2:C8)-SUMIF(B2:B8,"Debit",C2:C8).How to calculate a rolling 12-month total in Excel
=SUM(B2:B13)Sum the current month and the 11 before it, with a relative range:
=SUM(B2:B13)in the 12th month, filled down.Why does adding cells give a #VALUE! error in Excel
=SUM(B2:B7)One of the cells holds text, such as a space or "n/a", so use SUM, which skips text:
=SUM(B2:B7).How to sum across columns between two dates in Excel
=SUMIFS(B2:G2,$B$1:$G$1,">="&$K$1,$B$1:$G$1,"<="&$K$2)Use SUMIFS sideways, with the date header row as the criteria range:
=SUMIFS(B2:G2,$B$1:$G$1,">="&$K$1,$B$1:$G$1,"<="&$K$2).How to sum values by date in Excel
=SUMIF($A$2:$A$9,E2,$C$2:$C$9)List each date once and use SUMIF against the date column:
=SUMIF($A$2:$A$9,E2,$C$2:$C$9), filled down.Why doesn't a SUMIF wildcard work with numbers in Excel
=SUMPRODUCT((LEFT(A2:A8,LEN(E1))=E1&"")*B2:B8)Wildcards like "12*" only match text, so test the leading digits as text instead:
=SUMPRODUCT((LEFT(A2:A8,LEN(E1))=E1&"")*B2:B8).How to sum every N rows in Excel (groups of rows)
=SUMPRODUCT((INT((ROW($B$2:$B$16)-ROW($B$2))/$E$1)=D3-1)*$B$2:$B$16)Number the blocks with INT and add the rows in each:
=SUMPRODUCT((INT((ROW($B$2:$B$16)-ROW($B$2))/$E$1)=D3-1)*$B$2:$B$16).