To assist I redo my example data:
Date :Text :A :B
20180805:Dischem :BLANK :1233.39
20180805:Checkers :BLANK :606.71
20180901:PNP :2000.00 :BLANK
20181001:WW : BLANK :150.00
Dates are plain 8 digit numbers and the field separator is a colon.
Formula worked perfectly, HATS off to you Brian.
I noticed that dragging the formula onto lower cells ie 201810 and
201811 didn't increase te INT= value so I made that equal a cell on my
My final formula:
=SUMPRODUCT(INT($Data.$A$3:$A$30/100)=$A4,$Data.D$3:D$30) to display
it a sheet different to data
Tnx, I'll definitely be using the "INT" section alot more.
On 24 August 2018 at 15:28, Brian Barker <firstname.lastname@example.org> wrote:
At 08:59 24/08/2018 +0200, Hylton Conacher wrote:
I have data in 4 columns titled Date:Text:A:B. A typical row example is:
20180822:PNP:blank:500.00 OR 20180822:PNP:500.00:blank
A row will only have a value in column A or B, never both on the same row.
There may be multiple rows with the same date but different Text and A or B
1) How to SUM the values in column B ...
Er, that's column D, then?!
... for September i.e. 201809
That's easy: there are no such values, so the answer is zero!
Pointers and solutions GREATLY appreciated.
I'm having to guess that your date values are actually plain eight-digit
numbers; if they are date values formatted similarly, you will need to
modify my suggestion.
o A2:A99 is the array of date values (as integers).
o Dividing those by 100 and taking the integral part of the result -
INT(A2:A99/100) - gives the year and month values only.
o The expression INT(A2:A99/100)=201809 then gives an array of boolean
values, TRUE for September 2018 and FALSE otherwise.
o The SUMPRODUCT function then sums the numerical products of members of the
array of boolean values and corresponding members in column D (your "B"). In
this context, the boolean values TRUE and FALSE are interpreted as 1 and 0
respectively, so September 2018 values will be included but others not.
Note: although SUMPRODUCT() handles arrays it returns a single value, so it
is not necessary to enter it as an array function.
I trust this helps.
To unsubscribe e-mail to: email@example.com
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy