All resources related to SUM for Excel and Google Sheets.

Sum every n rows

When we don’t want to sum all the rows in a worksheet, and instead, we might only be interested in specific rows. To get the sum of every n rows in Excel, we can use a combination of OFFSET and SUM functions. Formula The generic formula to sum every n...

Subtotal invoices by age

Excel allows us to create subtotal for invoices by age, using the SUMIF function. This step by step tutorial will assist all levels of Excel users in creating subtotal for invoices by age. Figure 1. The final result of subtotal invoices by age Syntax of the SUMIF formula =SUMIF(range, criteria,...

Sum if cells contain an asterisk

Summing cells that contain an asterisk is tricky in Excel because the asterisk “*” is a wildcard. In Excel, it means any string of characters. This article will teach us how to sum cells containing an asterisk using SUMIF and the tilde (~). Figure 1. Final result: Sum if...

Sum Top n Values with Criteria

Figure 1. of Sum Top Values with Criteria. In order to get the sum of the top n values within an array of matching data values, we are going to utilize a formula which is derived from the Excel LARGE Function, embedded within the Excel SUMPRODUCT Function. Generic Formula =SUMPRODUCT(LARGE((range=criteria)*(values),{1,2,3,N}))...

How to Calculate the Sum by Week in Excel

We can now use a formula based on the excel SUMIFS function to sum by week. This article provides a clear guide on how you can use the function to sum by week. Figure 1: How to sum by week General syntax of the formula =SUMIFS(value, date, “>=”&A1, date, “<”&A1+7)...

Sum by weekday

Excel allows us to sum the values based on the weekday by using SUMPRODUCT and WEEKDAY functions. This step by step tutorial will assist all levels of Excel users in summarizing the values based on the weekday. Figure 1. Sum Amount by Weekday Syntax of the SUMPRODUCT Formula for Summing...

Sum if equal to either x or y

While working with Excel, we are able to sum values that satisfy more than one criteria by using the SUMIF, SUM and SUMPRODUCT functions. This step by step tutorial will assist all levels of Excel users in summing values that may contain either of two given criteria, employing three different...

How to Sum Visible Rows in a Filtered List

We can sum only the VISIBLE rows (ROWS NOT FILTERED OUT) in a filtered list with the SUBTOTAL FUNCTION with function number 9 or 109. This function automatically disregards hidden rows in a filtered list or table. The steps below will walk through the process. Figure 1: How to Sum...

How to Sum if One Criteria Multiple Columns

We can sum multiple columns conditionally by specifying a criterion. We can do this with a formula that is based on the SUMPRODUCT function. The steps below will walk through the process. Figure 1: Result of the Sum of Multiple Columns with Football as the Criterion Basic Formula =SUMPRODUCT((criterion_range=”football”)*(sum_range)) Formula...

Sum if cells contain both x and y

Excel allows us to sum all values from a table that contain both x and y by using the SUMIFS function. This step by step tutorial will assist all levels of Excel users in summing values from a table with two conditions. Figure 1. The final result of the SUMIFS...