[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.
=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]
Reason: Added green tick [RoryOF, Moderator]
Windows 10 Pro 1809 / OpenOffice 4.1.5
Re: SUm IF with a date
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: SUM IF with a date
... and with US English locale setting.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")
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
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.
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.
Windows 10 Pro 1809 / OpenOffice 4.1.5
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.
I did the same sum ifs, based on the vehicle and it worked perfectly.
Windows 10 Pro 1809 / OpenOffice 4.1.5
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 allmay march.
(Please don't again attach an image in such a case. Attach a spreadsheet file.)
You most likely wanted to add for all
(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
---
Lupp from München
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.
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
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.
-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 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
---
Lupp from München
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.
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.
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.
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.
Simple example: download/file.php?id=29471
Tutorial: https://wiki.openoffice.org/wiki/Docume ... /DataPilot
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
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!
I'll have to use that month function in the future though!
Thanks Guys!
Windows 10 Pro 1809 / OpenOffice 4.1.5
Re: [Solved] SUM IF with a date
Don't miss to also mind the YEAR.
- Attachments
-
- aooSumifs_2.ods
- (12.78 KiB) Downloaded 149 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
---
Lupp from München