[Solved] SUMIFS in spreadsheet

Discuss the spreadsheet application

[Solved] SUMIFS in spreadsheet

Postby MarkF » Wed Oct 13, 2021 4:38 am

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")
Last edited by Hagar Delest on Fri Oct 15, 2021 4:00 pm, edited 1 time in total.
Reason: tagged solved.
Open office 4.1.11
Windows 10
MarkF
 
Posts: 7
Joined: Wed Oct 13, 2021 4:21 am

Re: sumifs in spread sheet

Postby MrProgrammer » Wed Oct 13, 2021 5:03 am

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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 4109
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: SUMIFS in spreadsheet

Postby MarkF » Wed Oct 13, 2021 2:07 pm

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
MarkF
 
Posts: 7
Joined: Wed Oct 13, 2021 4:21 am

Re: SUMIFS in spreadsheet

Postby Lupp » Wed Oct 13, 2021 4:30 pm

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.
Last edited by Lupp on Wed Oct 13, 2021 5:01 pm, edited 2 times in total.
On Windows 10: LibreOffice 7.2 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3135
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: SUMIFS in spreadsheet

Postby Villeroy » Wed Oct 13, 2021 4:45 pm

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

Re: SUMIFS in spreadsheet

Postby Lupp » Wed Oct 13, 2021 4:59 pm

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 7.2 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3135
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: SUMIFS in spreadsheet

Postby MarkF » Fri Oct 15, 2021 3:28 pm

All is working well Thanks all
Open office 4.1.11
Windows 10
MarkF
 
Posts: 7
Joined: Wed Oct 13, 2021 4:21 am


Return to Calc

Who is online

Users browsing this forum: Acidello and 14 guests