Apache OpenOffice Community Forum

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")

MarkF wrote:I believe sumifs is what i want.

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 am getting error 508 …

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.
[Tutorial] Calc formula terms

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")

For numeric 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)

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

MarkF wrote:… each category in columns F and G …

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.

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.

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....

MarkF wrote:... because there will be a separate sheet in the book for each month.

Why?
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.

"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.

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...")

All is working well Thanks all