I am new to open office and I am trying to create a new formula. I believe sumifs is what i want. Column d is for the dollar amount. column a is the date for the year. looking at the spreadsheet it shows "07/01/21" but when click the cell it shows "07/01/2021". Column f and g have various categorizes. I want to sum the dollar amount using a month rage(example, July) and each category in columns f and g. ultimately sheet a will pull this date from sheet b. but right now i am just trying everything in sheet b for simplicity. below is one version i tried. I am getting error 508 right now
=SUMIFS($D$18:$D$2000;$A$18:$A$2000,>= DATE("07/01/21");$A$18:$A$2000,< DATE("8/1/21");$F$18:$F$2000, = "4";$G$18:$G$2000, = "51")
[Solved] SUMIFS in spreadsheet
[Solved] SUMIFS in spreadsheet
Last edited by Hagar Delest on Fri Oct 15, 2021 4:00 pm, edited 1 time in total.
Reason: tagged solved.
Reason: tagged solved.
Open office 4.1.11
Windows 10
Windows 10
- MrProgrammer
- Moderator
- Posts: 4904
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: sumifs in spread sheet
SUMIFS is cumbersome and feeble. Is the data in F and G numeric or text? Read section 1. Types of data in Ten concepts that every Calc user should know. Only use quotes in the formula if the values in the column are text.MarkF wrote:I believe sumifs is what i want.
The only valid use for a comma in an OpenOffice Calc formula is as a decimal sign if your locale uses commas to separate the integer and fractional parts of numbers, as is used in many countries where π is written 3,14159…. You are incorrectly putting a comma after the ranges in your formula.MarkF wrote:I am getting error 508 …
[Tutorial] Calc formula terms
For numeric data try:MarkF wrote:=SUMIFS($D$18:$D$2000;$A$18:$A$2000,>= DATE("07/01/21");$A$18:$A$2000,< DATE("8/1/21");$F$18:$F$2000, = "4";$G$18:$G$2000, = "51")
=SUMPRODUCT($D$18:$D$2000;YEAR($A$18:$A$2000)=2021;MONTH($A$18:$A$2000)=7;$F$18:$F$2000=4;$G$18:$G$2000=51)
or =SUMPRODUCT($D$18:$D$2000;TEXT($A$18:$A$2000;"YYYYMM")="202107";$F$18:$F$2000=4;$G$18:$G$2000=51)
For text data try:
=SUMPRODUCT($D$18:$D$2000;YEAR($A$18:$A$2000)=2021;MONTH($A$18:$A$2000)=7;$F$18:$F$2000="4";$G$18:$G$2000="51")
or =SUMPRODUCT($D$18:$D$2000;TEXT($A$18:$A$2000;"YYYYMM")="202107";$F$18:$F$2000="4";$G$18:$G$2000="51")
[Tutorial] The SUMPRODUCT function
Each?? If you use a Pivot Table you can get summaries for all the combinations of values in F and G without writing any formulas.MarkF wrote:… each category in columns F and G …
For any additional assistance, attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Explain your goal.
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: SUMIFS in spreadsheet
a quick test and the numeric appears to work. I didn't think of a pivot table because there will be a separate sheet in the book for each month. i am open to ideas though....
Open office 4.1.11
Windows 10
Windows 10
Re: SUMIFS in spreadsheet
Why?MarkF wrote:... because there will be a separate sheet in the book for each month.
This may cause annoyances again and again.
Hiding or freezing by protection parts of a sheet (no longer or not yet of much meaning) is simple and safe. Joint evaluation of many sheets is complicated and error-prone.
BTW: Never design formulas specialized to (depending on the usage of) a "localized" date format. Dates must either be represented as numbers based on the NullDate setting of the sheets, or as texts in the negotiated and well considered format standardized as ISO 8601 extended.
In specific slash-separated dates are basically ambiguous. The English(UK) and the English(USA) usages are incompatile. One of the date strings you used (07/01/21) will mean 2021-01-07 in an "UK sheet", but 2021-07-01 in "US sheet". And if a person set his (f/m) sheet to a different "two-digit-year-century" you may enjoy the date-format-jam to its full extent. Never use two-digit-year format except when writing a letter to grand-aunt Rose.
Last edited by Lupp on Wed Oct 13, 2021 5:01 pm, edited 2 times 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: SUMIFS in spreadsheet
"07/01/21" is not a date. It is a meaningless string with 6 digits and 2 slashes.
"2021-01-07" could be interpreted as a date or "2021-07-01". Nobody knows if your "07/01/21" refers to a day in July or January. VALUE("07/01/21") may work somehow but it returns a different value depending on the locale setting because of the ambiguity of the given string. You may get 1st of July whereas your co-worker may get 7th of January.
The DATE function does not accept any string argument. DATE takes 3 numbers specifying the year, month and day as in =DATE(2021;1;7).
Yes, I aggree. Splitting equally structured data across sheets is always a mistake and pivot tables solve all these "problems" with pseudo-databases on sheets.
"2021-01-07" could be interpreted as a date or "2021-07-01". Nobody knows if your "07/01/21" refers to a day in July or January. VALUE("07/01/21") may work somehow but it returns a different value depending on the locale setting because of the ambiguity of the given string. You may get 1st of July whereas your co-worker may get 7th of January.
The DATE function does not accept any string argument. DATE takes 3 numbers specifying the year, month and day as in =DATE(2021;1;7).
Yes, I aggree. Splitting equally structured data across sheets is always a mistake and pivot tables solve all these "problems" with pseudo-databases on sheets.
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: SUMIFS in spreadsheet
Unfortunately the DATEVALUE() function seems to accept such malformed strings - and by that to tempt users to stick to outdated and fundamentally improper formats. A cell having "translated" such a string to a numeric date representation may show a different date a few years later when the default century was shifted (by default again) and by that make the sheets show completely wrong results without a warning. ("Till yesterday everything worked as expected...")
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