CODEWITHSUNDEEP
libreoffice-calc
Hylton ZR1HPC
Hylton ZR1HPC

Reputation: 1

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 namely 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:

1) How to calculate the total figure in column B for August ie 201808? 2) How to calculate the total figure in column A for Auhust and September months?

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 ie 201808 or 201809. Vlookup and Hlookup have also not assisted as whilst they can return a single value, Summing multiple values is an issue.

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

Pointers and solutions GREATLY appreciated.

Hylton

Upvotes: 0

Views: 100

Answers (1)

pnuts
pnuts

Reputation: 59485

A helper column would make things very easy. Assuming your column A is ColumnC, insert a new ColumnA and populate it with:

=LEFT(B1,6)

copied down to suit. Then for:

How to calculate the total figure in column B for August ie 201808?

=SUMIF(A:A,"201808",E:E)

How to calculate the total figure in column A for Auhust and September months

=SUMIF(A:A,"201808",D:D)+SUMIF(A:A,"201809",D:D)

Upvotes: 0

Related Questions