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

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