Date: prev next · Thread: first prev next last
2018 Archives by date, by thread · List index


Greetings Brian,

To assist I redo my example data:

Date        :Text              :A          :B
20180803:Cattle Baron:430.00:BLANK
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.

Try =SUMPRODUCT(INT(A2:A99/100)=201809;D2:D99)

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

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.

Regards
Hylton

On 24 August 2018 at 15:28, Brian Barker <b.m.barker@btinternet.com> 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
value.

Example Data:
20180803:Cattle Baron:430.00:
20180805:Dischem::1233.39
20180805:Checkers::606.71
20180901:PNP:2000.00:
20181001:WW::150.00

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.

Try =SUMPRODUCT(INT(A2:A99/100)=201809;D2:D99)

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

Brian Barker


-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: 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.