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.bar...@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+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ Privacy Policy: http://www.documentfoundation.org/privacy
