Excel help: sum numbers if within date range

Question

I have a spreadsheet of expenses, sorted by categories in columns C through P (eg, "rentals" in column C, "catering" in column D, etc). The date is in column A. I want to find the sum of all expenses in columns C:P, but only those where the date is between a certain range. Here's what I tried:

=SUMIFS(C:P,A:A,">=1-Feb-2018",A:A,"<=28-Feb-2018")

but I get a #VALUE! error. I think it is because C:P is a different size selection than A:A? It works if I do

=SUMIFS(C:C,A:A,">=1-Feb-2018",A:A,"<=28-Feb-2018")

Is there another formula that will work without using

= SUMIFS(C:C,A:A,">=1-Feb-2018",A:A,"<=28-Feb-2018")
 +SUMIFS(D:D,A:A,">=1-Feb-2018",A:A,"<=28-Feb-2018")
 +SUMIFS(E:E,A:A,">=1-Feb-2018",A:A,"<=28-Feb-2018")...

Alexis Olson · Accepted Answer · 2018-03-01 00:55:37Z

1

I'm not sure if you can get it to work with a SUMIFS, but here's a SUMPRODUCT version:

=SUMPRODUCT(C:P * (A:A <= DATE(2018,2,28)) * (A:A >= DATE(2018,2,1)))

You could also create a helper column, say Q, that sums C:P and use your SUMIFS on that.

=SUMIFS(Q:Q, A:A, ">=1-Feb-2018", A:A, "<=28-Feb-2018")

answered Mar 1, 2018 at 0:55

Alexis Olson

40.4k8 gold badges51 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Forward Ed Over a year ago

Because sumproduct performs array like operations, full column references should be avoided as it can lead to excessive unnecessary calculations.

Chronocidal · Accepted Answer · 2018-03-01 09:41:09Z

This is because 1-Feb-2018 is not recognised by Excel as a date - it will be giving a #Name? error inside the formula, as it's not even a String (">=""1-Feb-2018"""))

1st February 2018 is treated by Excel as the number 43132 (Number of days since 31/12/1899), but is then Formatted as d-mmm-yyyy to display as 1-Feb-2018. You can either:

Type the number: ">=43132"
Add 0 to a string version to convert it: ">=(0+""1-Feb-2018"")"
Use the DATE function: ">=" & DATE(2018,2,1)

All 3 of these work with SUMIFS, like so:

=SUMIFS(C:P, A:A, ">=43132", A:A, "<=43159")
=SUMIFS(C:P, A:A, ">=(0+""01-Feb-2018"")", A:A, "<=(0+""28-Feb-2018"")")
=SUMIFS(C:P, A:A, ">=" & DATE(2018,2,1), A:A, "<=" & DATE(2018,2,28))

Personally, I recommend method 3

Collectives™ on Stack Overflow

Excel help: sum numbers if within date range

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related