[SOLVED] Conditional array sum?

Discuss the spreadsheet application

[SOLVED] Conditional array sum?

Postby Ssronica » Mon Feb 10, 2020 5:46 pm

Apologies if this newbie has the wrong terminology. Been researching this one and I'm clearly out of my depth.

What I want to do is fairly simple: column B has a series of dates (not necessarily in ascending order). Column D has amounts. I would like to add the figures in column D that are only between certain dates.

I've attached a sample spreadsheet, in which I want to add all amounts occurring for example, between 7th January and 17th January.

Many thanks for any help at all!
Attachments
testdateamount.ods
(9.79 KiB) Downloaded 15 times
Last edited by robleyd on Tue Feb 11, 2020 12:19 am, edited 2 times in total.
Reason: Add green tick
OpenOffice 3.4.1;Win7
Ssronica
 
Posts: 33
Joined: Wed Sep 25, 2013 11:41 pm

Re: Conditional array sum?

Postby Zizi64 » Mon Feb 10, 2020 6:21 pm

SUMIFS()

testdateamount_Zizi64.ods
(9.79 KiB) Downloaded 15 times


The Highlights values function is switched on - to check the type of your data.

Similar topic:
viewtopic.php?f=9&t=89368
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9555
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [SOLVED] Conditional array sum?

Postby Ssronica » Mon Feb 10, 2020 6:46 pm

Sensational - thank you! It never ceases to amaze me how skilled you guys are, and how helpful.

I have marked this as solved.

Köszönöm!
OpenOffice 3.4.1;Win7
Ssronica
 
Posts: 33
Joined: Wed Sep 25, 2013 11:41 pm

Re: Conditional array sum?

Postby Lupp » Mon Feb 10, 2020 6:48 pm

You may also Use SUMPRODUCT(). Avoiding the need of introducing comparing operators as texts it is slightly better structured, imo.

Unsolicited advices:
Avoid empty rows (and the every-other-row-design).
Don't explicitly set an alignment for data columns. It spoils the very useful way Calc distinguishes texts and numbers for the display. Thinking something entered as text is a number has the power to cause serious malfunction.
Use standards, don't be stubborn. This in specific concerning dates where a very well considered international standard exists: ISO 8601. The format I mean has the code
Code: Select all   Expand viewCollapse view
YYYY-MM-DD
in Calc.
Attachments
aoo101049conditionalSum_1.ods
(11.02 KiB) Downloaded 15 times
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2918
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [SOLVED] Conditional array sum?

Postby Ssronica » Mon Feb 10, 2020 6:56 pm

Lupp: danke.

I didn't see a functional difference between SUMPRODUCT and SUMIFS, so I'll just stick with Zizi's response for now.

I have been told about the dangers of empty rows, but it's sort of necessary for the actual spreadsheet I'm using.

I didn't understand this: 'Don't explicitly set an alignment for data columns' - it sounds like you're saying just stick to the date standard so Calc doesn't mistake the data entered for something else.
OpenOffice 3.4.1;Win7
Ssronica
 
Posts: 33
Joined: Wed Sep 25, 2013 11:41 pm

Re: [SOLVED] Conditional array sum?

Postby RusselB » Mon Feb 10, 2020 7:25 pm

The functionality differences come into play with more complex comparisons/calculations than what you have.
For the empty rows, most people have them so they can read the information easier, not the spreadsheet program.
Alignment is for the left/right side of the cell and has nothing to do with the date format as indicated.
OpenOffice 4.1.7 and LibreOffice 6.3.3.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: 6123
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: [SOLVED] Conditional array sum?

Postby Lupp » Mon Feb 10, 2020 10:57 pm

Concerning the horizontal alignment:
The default setting (NO horizontal alignment set explicitly) shows texts left aligned and numbers (including dates, Boolean values, numbers formatted in any way) right aligned. Accepting this default, a probable error caused by entering something not recognized as a number and therefore being processed as text would be obvious even if the text LOOKS numeric (including dates, currency values and the like again).

An example: In column A you have the cell A2 either containing or having calculated the value 45 (as a number). The cell is formatted to show this in the format given by the format code
Code: Select all   Expand viewCollapse view
"$"0.00
. This reads as "$45.00". In the cell A3 below you entered the text "$45.00" and it was not recogniszed as a number. It looks exactly the same as the formatted value. Using the default alignment Calc will advertise the fact by left aligning the entered text. This CAN be very important to avoid hidden errors. SUM(A2:A3) e.g. will give the value 45 instead of 90 (which again may be formatted in any way! SUM() ignores any texts occourring in cells of the range it is applied to. Simply make use of the well considered advantages Calc gives you. (Centered alignment is bad for additiional reasons. If you want to have a clear distance between the right end of something displayed and the cell grid line right of it, make the explicit setting under 'Borders'.)
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2918
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 23 guests