[Solved] SUM IF with a date

Discuss the spreadsheet application
Post Reply
MCR
Posts: 8
Joined: Fri Apr 12, 2019 11:10 pm

[Solved] SUM IF with a date

Post by MCR »

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
FJCC
Moderator
Posts: 9231
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: SUm IF with a date

Post by FJCC »

With the data and formula on one sheet, this works for me.

Code: Select all

=SUMIFS(K6:K13;D6:D13;"<12/25/2019";D6:D13;">01/31/2019")
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SUM IF with a date

Post by Villeroy »

FJCC wrote:With the data and formula on one sheet, this works for me.

Code: Select all

=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

=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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
MCR
Posts: 8
Joined: Fri Apr 12, 2019 11:10 pm

Re: SUM IF with a date

Post by MCR »

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
Screenshot (6).png
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

Post by MCR »

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
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: SUM IF with a date

Post by Lupp »

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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
MCR
Posts: 8
Joined: Fri Apr 12, 2019 11:10 pm

Re: SUM IF with a date

Post by MCR »

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
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: SUM IF with a date

Post by Lupp »

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
(11.63 KiB) Downloaded 139 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: SUM IF with a date

Post by RusselB »

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, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SUM IF with a date

Post by Villeroy »

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.
Simple example: download/file.php?id=29471
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
MCR
Posts: 8
Joined: Fri Apr 12, 2019 11:10 pm

Re: SUM IF with a date

Post by MCR »

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
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] SUM IF with a date

Post by Lupp »

Don't miss to also mind the YEAR.
Attachments
aooSumifs_2.ods
(12.78 KiB) Downloaded 143 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply