Add column values according to part of date value

users
#1

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

#2

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

#3

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

#4

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