Author: Oscar Cronquist Article last updated on August 27, 2019

The SUMIFS function in cell D11 adds numbers from column D based on criteria applied to column B and C.

=SUMIFS(D3:D8,B3:B8,B11,C3:C8,C11)

Excel Function Syntax

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)

Arguments

sum_range Required. A cell reference to a cell range whose numbers you want to sum.
criteria_range1 Required. The cell range you want to test Criteria1 for.
criteria1 Required. The condition you want to use applied to criteria_range1 to sum the corresponding cells in sum_range
[criteria_range2] Optional. Up to 127 additional arguments.
[criteria2] Optional. Up to 127 additional arguments.

 

The SUMIFS function in cell D11 sums all corresponding values that begin with B in column B and is Small in column C.

=SUMIFS(D3:D8,B3:B8,B11,C3:C8,C11)

You can use wildcard characters like:

  • * (asterisk) - Matches any length of characters
  • ? (question mark) - Matches any single character

The formula in cell D11 sums numbers in column D based on numbers less than 104 in column B and Small in column C.

=SUMIFS(D3:D8,B3:B8,B11,C3:C8,C11)

You are also allowed to use logical operators like:

  • > larger than
  • < smaller than
  • = equal to
  • <> not equal to

Download Excel *.xlsx file

SUMIFS function.xlsx

How do I sum values between k-th weekday this month and k-th weekday next month?

Alan asks:

I have this formula

(=SUMIFS($C$14:$C$1000,$A$14:$A$1000,">="&DATE($A$1,8,1),$A$14:$A$1000,"<"&DATE($A$1,9,1),$F$14:$F$1000,$AA1))

which works but I want it to show from the 4th Sunday of a month to the 4th Sunday of the next month.

Formula in cell E3:

=SUMIFS(C3:C28, B3:B28, ">="&B3+(7-WEEKDAY(B3, 2))+21, B3:B28, "<="&DATE(YEAR(B3), MONTH(B3)+1, 1)+(7-WEEKDAY(DATE(YEAR(B3), MONTH(B3)+1, 1), 2))+21)

Explaining formula in cell E3

Step 1 - Calculate 4-th sunday this month

To calculate the fourth sunday we must first calculate the first Sunday in current month. Cell B3 contains this date 1/5/2019.

WEEKDAY(B3, 2) returns a number representing the weekday. 1 is Monday, 2 is Tuesday and so on.. 7 is Sunday.

WEEKDAY(B3, 2)

becomes

WEEKDAY(1/5/2019, 2)

returns 6.

7 minus 6 equals 1. We must add 1 to the date in cell B3 to get the first Sunday in that month. (This calculation works only if the date in cell B3 is less or equal to the date of the first Sunday in that month.)

B3+(7-WEEKDAY(B3, 2))+21

becomes

1/5/2019+(7-WEEKDAY(B3, 2))+21

becomes

1/5/2019+1+21

To get the fourth Sunday we add 21 to the date of the first Sunday.

1/5/2019+1+21 equals 1/27/2019.

Step 2 - Calculate 4-th sunday next month

To get the first day of the next month we need to calculate the first date of this month and then add 1 to the month.

DATE(YEAR(B3), MONTH(B3)+1, 1)

becomes

DATE(2019, 1+1, 1)

becomes

DATE(2019, 2, 1)

and returns 2/1/2019. This date is also used to calculate the fourth Sunday.

DATE(YEAR(B3), MONTH(B3)+1, 1)+(7-WEEKDAY(DATE(YEAR(B3), MONTH(B3)+1, 1), 2))+21

becomes

2/1/2019+(7-5)+21

becomes

2/1/2019+23 equals 2/24/2019.

Step 3 - Build SUMIFS function

The SUMIFS function allows you to add values based on conditions.

SUMIFS(C3:C28, B3:B28, ">="&B3+(7-WEEKDAY(B3, 2))+21, B3:B28, "<="&DATE(YEAR(B3), MONTH(B3)+1, 1)+(7-WEEKDAY(DATE(YEAR(B3), MONTH(B3)+1, 1), 2))+21)

becomes

SUMIFS(C3:C28, B3:B28, ">="&1/27/2019, B3:B28, "<="&2/24/2019)

and returns 494.

60+70+52+37+70+93+22+90 = 494