## [Solved] SUM IF with a date

### [Solved] SUM IF with a date

I'm kinda confused Why its not giving me a rum for the range, I've tried Both Set of Quotation " or ' As well as the greater than/ less than symbol flipflopped etc. Also doesn't matter if the same formula is on the same sheet, or for lack of a better work transposing onto a the other sheet.

=SUMIFS('Rental Income'.K6:K136;'Rental Income'.D6:D136;">12/25/2019";'Rental Income'.D6:D136;">01/31/2019")

I know how to do the sumif with the criteria, being based on a cell, which is that date, how ever. That's not what I want in this instance.
Last edited by RoryOF on Mon Apr 15, 2019 10:54 pm, edited 2 times in total.
Reason: Added green tick [RoryOF, Moderator]
Windows 10 Pro 1809 / OpenOffice 4.1.5
MCR

Posts: 8
Joined: Fri Apr 12, 2019 11:10 pm

### Re: SUm IF with a date

With the data and formula on one sheet, this works for me.
Code: Select all   Expand viewCollapse view
`=SUMIFS(K6:K13;D6:D13;"<12/25/2019";D6:D13;">01/31/2019")`
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7452
Joined: Sat Nov 08, 2008 8:08 pm

### Re: SUM IF with a date

FJCC wrote:With the data and formula on one sheet, this works for me.
Code: Select all   Expand viewCollapse view
`=SUMIFS(K6:K13;D6:D13;"<12/25/2019";D6:D13;">01/31/2019")`

... and with US English locale setting.

This works with any locale setting:
Code: Select all   Expand viewCollapse view
`=SUMIFS(K6:K13;D6:D13;"<2019-12-25";D6:D13;">2019-01-31")`
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

Villeroy
Volunteer

Posts: 27610
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: SUM IF with a date

It's still not coming back right. It is returning a value of \$2,575.00
The correct total would be 1601.79.
=SUMIFS('Rental Income'.K6:K136;'Rental Income'.D6:D136;"<03/01/2019";'Rental Income'.D6:D136;"<03/31/2019")

=SUMIFS('Rental Income'.K6:K136;'Rental Income'.D6:D136;">03/01/2019";'Rental Income'.D6:D136;"<03/31/2019")
Returns a value of \$720.00- which is only Summing one of the two Records.
Attachments
Windows 10 Pro 1809 / OpenOffice 4.1.5
MCR

Posts: 8
Joined: Fri Apr 12, 2019 11:10 pm

### Re: SUM IF with a date

The Same is True for each Date range I'm Trying to Sum, Even if a use a cell for the criteria, instead of typing the date ranges in the actual formula. Anyone see where I'm going wrong.
I did the same sum ifs, based on the vehicle and it worked perfectly.
Windows 10 Pro 1809 / OpenOffice 4.1.5
MCR

Posts: 8
Joined: Fri Apr 12, 2019 11:10 pm

### Re: SUM IF with a date

2019-03-11 actually is within your given range. 2019-03-01 is not.

You most likely wanted to add for all may march.

(Please don't again attach an image in such a case. Attach a spreadsheet file.)
Last edited by Lupp on Mon Apr 15, 2019 10:01 pm, edited 1 time in total.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2621
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

### Re: SUM IF with a date

Yes, But I need a way to just do a entire month cycle for the total, So if say I'm not here and someone adds new records there no adjusting the formulas, I need to essentially be able to create a master sheet, and regardless of the actual dates, be able to sum, whatever within a monthly range.

Is there a better way of doing this, like I said, I've been using the sumifs on different criteria, other than dates and I can get that no problem.
Windows 10 Pro 1809 / OpenOffice 4.1.5
MCR

Posts: 8
Joined: Fri Apr 12, 2019 11:10 pm

### Re: SUM IF with a date

Well, we are talking about two things now:
-1- The correctness of the formula. My above remark was concerning this subject.
-2- An enhancement to a then correct formula allowing for easily creating a subtotal per a selected month.

The second subject is addressed in the attached demo file. I would suggest you use the SUMPRODUCT variant in cell G8.
Attachments
aooSumifs_1.ods
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2621
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

### Re: SUM IF with a date

Are your "dates" true dates (ie: a number) or are they text that looks like a date.
If a number, then you could use the MONTH function inside the SUMIF so that the comparison is based on the month, rather than the specific date.
This will require you to change how the formula is entered, as it will require forcing an array as a parameter where an array normally isn't used.
An alternative, is to use the SUMPRODUCT function. rather than the SUMIF function, and I seem to recall seeing a post earlier that is similar to what you are doing, where the SUMPRODUCT was used to resolve the problem.
I will look later to see if I can find the topic.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 5814
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: SUM IF with a date

There are much better ways to do this. All of them require that all users know how to do list keeping on spreadsheets so you don't need to adjust formulas. List keeping on a sheet requires that you insert a new row for every new record. All references in the same document adjust instantly to the new size of the list range. Turn on Tools>Options>Calc>General>"Expand references ...". Now the refereces will expand even when you insert new rows directly below the referenced range.

A pivot table is a table derived from a list. It is able to show all kinds of aggregations (sums, counts, averages,... ) for each category in a list, for each month, quarter, year of a date column.
Tutorial: https://wiki.openoffice.org/wiki/Docume ... /DataPilot
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

Villeroy
Volunteer

Posts: 27610
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: SUM IF with a date

I Finally got it, using start and end dates, in individual cells, as the criteria! it wasn't working prior because the Formats as someone suggested were different between the sheets. I never did get, just using the dates in the formulas to work though.
I'll have to use that month function in the future though!

Thanks Guys!
Windows 10 Pro 1809 / OpenOffice 4.1.5
MCR

Posts: 8
Joined: Fri Apr 12, 2019 11:10 pm

### Re: [Solved] SUM IF with a date

Don't miss to also mind the YEAR.
Attachments
aooSumifs_2.ods
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2621
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany