Add column values according to part of date value

classic Classic list List threaded Threaded
4 messages Options
zr1hpc zr1hpc
Reply | Threaded
Open this post in threaded view
|

Add column values according to part of date value



Using LibreOffice Calc 6.0.6.2 on macOS High Sierra.

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 for September i.e. 201809

I have tried vlookup coupled with IF and RANDBETWEEN, SUMIF. I have
Googled myself silly looking for a way to SUM multiple values that meet
a criteria i.e. 201808 or 201809. Vlookup and Hlookup have also not
assisted as whilst they can return a single value, the addition of
multiple values is an issue.

My thinking goes " SUM those values in the array a1:d5 that have the
first column beginning with "201808" Putting that into a formula is
what's getting me.

Pointers and solutions GREATLY appreciated.

Hylton

--
To unsubscribe e-mail to: [hidden email]
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
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: 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: [hidden email]
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

zr1hpc zr1hpc
Reply | Threaded
Open this post in threaded view
|

Re: Add column values according to part of date value

In reply to this post by zr1hpc
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 <[hidden email]> 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: [hidden email]
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
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Add column values according to part of date value

At 17:19 24/08/2018 +0200, Hylton Conacher wrote:
>Formula worked perfectly, ...

Good-oh!

>... HATS off to you Brian.

Thanks, but hats off to the SUMPRODUCT() function really.

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

You might prefer to enter your dates as real dates, which could be
formatted the way you have them using "YYYYMMDD" as the format
string. Then you could extract year and month using the YEAR() and
MONTH() functions:
=SUMPRODUCT(YEAR(A3:A30)=2018;MONTH(A3:A30)=9;D3:D30)

Brian Barker  


--
To unsubscribe e-mail to: [hidden email]
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