CODEWITHSUNDEEP
excelexcel-formuladate-rangesumifs
Elizabeth Cormode
Elizabeth Cormode

Reputation: 11

Excel help: sum numbers if within date range

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")...

Upvotes: 1

Views: 511

Answers (2)

Chronocidal
Chronocidal

Reputation: 7951

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:

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

All 3 of these work with SUMIFS, like so:

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

Personally, I recommend method 3

Upvotes: 1

Alexis Olson
Alexis Olson

Reputation: 40204

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")

Upvotes: 1

Related Questions