Re: [libreoffice-users] Add column values according to part of date value

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