[Solved] SUM IF with a date

Discuss the spreadsheet application

[Solved] SUM IF with a date

Postby MCR » Fri Apr 12, 2019 11:18 pm

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

Postby FJCC » Sat Apr 13, 2019 12:16 am

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
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7263
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: SUM IF with a date

Postby Villeroy » Sat Apr 13, 2019 11:12 am

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
User avatar
Villeroy
Volunteer
 
Posts: 27114
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SUM IF with a date

Postby MCR » Mon Apr 15, 2019 7:09 pm

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

Postby MCR » Mon Apr 15, 2019 7:13 pm

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

Postby Lupp » Mon Apr 15, 2019 7:54 pm

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

Re: SUM IF with a date

Postby MCR » Mon Apr 15, 2019 9:32 pm

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

Postby Lupp » Mon Apr 15, 2019 10:00 pm

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 11 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: SUM IF with a date

Postby RusselB » Mon Apr 15, 2019 10:05 pm

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.0.6.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.
User avatar
RusselB
Moderator
 
Posts: 5414
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: SUM IF with a date

Postby Villeroy » Mon Apr 15, 2019 10:11 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27114
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SUM IF with a date

Postby MCR » Mon Apr 15, 2019 10:46 pm

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

Postby Lupp » Mon Apr 15, 2019 11:53 pm

Don't miss to also mind the YEAR.
Attachments
aooSumifs_2.ods
(12.78 KiB) Downloaded 17 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 20 guests