How to sum multiple rows and columns in Excel with criteria

Sum Amounts in a Date RangeTo sum amounts based on a date range, you can use the SUMIFS function in Excel 2007 or later versions. Watch this video to see the steps, and the written

How to sum multiple rows and columns in Excel with criteria

Sum Amounts in a Date Range

To sum amounts based on a date range, you can use the SUMIFS function in Excel 2007 or later versions. Watch this video to see the steps, and the written instructions are below the video.

Sum Amounts in a Date Range

To total the amounts in a specific date range, use the SUMIFS function (Excel 2007 and later) or the SUMIF function. There are two examples below:

- Total with SUMIFS

- Total with SUMIF

In this example, a Start date and an End date are entered on the worksheet. Dates are in column A, and units sold are in column B.

See more Date Range examples on the Sum or Count for a Date Range page.

Excel Sum date range

Use SUMIFS to Calculate Total for a Date Range

For Excel 2007, and later versions, you can use the SUMIFS function to calculate a total based on multiple criteria. We'll use a SUMIFS formula to total all the units where the sales date is:

  • on or after the Start date
  • on or before the End date.

Here is the formula that is entered in cell D5:

=SUMIFS($B$2:$B$9,$A$2:$A$9,">=" & $D$2, $A$2:$A$9,"<=" & $E$2)

  • The first argument, $B$2:$B$9, is the range with the numbers that we want to sum.
  • The 2nd argument, $A$2:$A$9, is the range to check for criteria 1.
  • The 3rd argument, ">=" & $D$2, is the range with the value for criteria 1 (the Start date), and the operator to use with that value (greater than or equal to)
  • The 4th argument, $A$2:$A$9, is the range to check for criteria 2.
  • The 5th argument, "<=" & $E$2, is the range with the value for criteria 2 (the End date), and the operator to use with that value (less than or equal to)
Excel Sum date range SUMIFS

In this example, the result for the selected date range is a total of 494 units sold. To verify, you can select cells B3:B6, and look at the total shown in Excel's Status Bar.

Excel Sum date range verify

To get the total units for a different date range, change the Start date in cell D2, and/or the End date in cell E2.

Use SUMIF to Calculate Total for a Date Range

For Excel 2003, and earier versions, you can use the SUMIF function to calculate a total based on a single criterion. We'll use one SUMIF formula to total all the units where the sales date is:

  • on or after the Start date

Then we'll use another SUMIF formula to subtract any values where there date is

Here is the formula that is entered in cell D5:

=SUMIF($A$2:$A$9,">=" &$D$2,$B$2:$B$9)
- SUMIF($A$2:$A$9,">" &$E$2,$B$2:$B$9)

  • The range, $A$2:$A$9, contains the numbers that we want to sum.
  • The criteria, ">=" & $D$2, is the range with the Start date, and the operator to use with that value (greater than or equal to)
  • The range, $B$2:$B$9, is the range to check for the date
  • The criteria, ">" & $E$2, is the range with the End date, and the operator to use with that value (greater than)
Excel Sum date range SUMIF

In this example, the result for the selected date range is a total of 494 units sold. To verify, you can select cells B3:B6, and look at the total shown in Excel's Status Bar.

Excel Sum date range verify

To get the total units for a different date range, change the Start date in cell D2, and/or the End date in cell E2.

Video liên quan