Not sure if I fully understand what you are looking at, but here is what I have attempted. Assuming you column data is: A is month B is day of Month C is the rain on day in 2016 D is the rain on day in 2017
I've added Column E and Column F to be real date value. E2 =DATE(2016,MATCH(A2,$G$1:$G$12),B2) F2 =DATE(2017,MATCH(A2,$G$1:$G$12),B2) Copied for all lines in column A-D. Since the month has to match exactly, I put the month names in G1-G12 these would need to match with how you entered months. In B13 put Days In C13 forumula to get number of days with rain in range. =COUNTIFS($C$2:$C$5,">0",$E$2:$E$5,">"&DATE(YEAR(TOD AY())-1,MO NTH(TODAY()),DAY(TODAY()))) First part is to not count days with 0 values, Second part is to only get the date range with year - 1. (You could subtract on from day, but that would cause issue on 1st, so would probable need to convert date to julian format and subtract 1) In D13 =COUNTIFS(D2:D5,">0",$F$2:$F$5,"<="&TODAY()) Put Rain in B13 In C13 sum total =SUMIF($E$2:$E$5,"<="&DATE(YEAR(TODAY())-1,MONTH(TO DAY()),DAY (TODAY())),$C$2:$C$5) Doesnt' matter if we sum up zero entries. In D13 =SUMIF($F$2:$F$5,"<="&TODAY(),$D$2:$D$5) Results I get are 1 day in 2016 with total of 1mm of Rain. Results for 2017 is 2 days with 16mm of Rain. My sheet is on one of my college servers. ftp://fedora9gcc.dyndns.org/rain.ods On 7 May 2017 at 21:55, Hylton Conacher (ZR1HPC) wrote: Send reply to: hyl...@conacher.co.za To: LibreOffice Users <users@global.libreoffice.org> From: "Hylton Conacher (ZR1HPC)" <hyl...@conacher.co.za> Subject: [libreoffice-users] COUNTIF / DAYS / ? Date sent: Sun, 7 May 2017 21:55:45 +0200 > Hi, > > I have a spreadsheet I use to draw graphs and extract information about > the rainfall in my area for the last two years. > > I need to create a formula that will count how many days it rained > between the start of the year, both last year and this year, so I can > compare the amount of rainfall that was received during that time frame. > > The example below is a sample dataset that shows how much rainfall was > received in 2016 or 2017, on each of the four days > > Example: > Month Day 2016 2017 > Jan 1 0 15 > Feb 29 1 0 > May 6 0 1 > Sept 22 15 5 > > From this we can determine that 16mm of rain fell in 2016 over 2 days. > Likewise we can determine that 21mm of rain fell in 2017 but over 3 days. > > Today is the 7th of May 2017 and I would like a formula to work out how > many days the rainfall received between(and incl) 2016/01/01 and the day > before TODAY last year. The answer for 2016 is 1 i.e. it only rained > once between the dates specified, however the answer for 2017 is 16 as > it rained twice between TODAY-1 and 2017/1/1. I had thought of using > COUNTIF or DAYS or =COUNTIF(C2:SUM(TODAY()-1,">0")), however the problem > is that I do not have a single date column, but three. > > This formula will allow me to create a graph showing how much rainfall > had fallen last year compared to this year between the beginning of the > year(01/01) and the day prior to Today. > > I had thought of converting the first two columns into a single and > having the text name of the month with each successive line being a new > date in that month until the month changed, however I was unable to get > it to work i.e. automatically change month after 31 days in January to > read February 01. > As you can imagine I have 367 rows of data per year, mostly with 0 as a > value, however there are odd days it does rain. > > Any comments, and if you want the original spreadsheet, just yell where > to put it i.e. Nabble etc. > > Regards > Hylton > > -- > 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/ > All messages sent to this list will be publicly archived and cannot be deleted +----------------------------------------------------------+ Michael D. Setzer II - Computer Science Instructor Guam Community College Computer Center mailto:mi...@guam.net mailto:msetze...@gmail.com Guam - Where America's Day Begins G4L Disk Imaging Project maintainer http://sourceforge.net/projects/g4l/ +----------------------------------------------------------+ http://setiathome.berkeley.edu (Original) Number of Seti Units Returned: 19,471 Processing time: 32 years, 290 days, 12 hours, 58 minutes (Total Hours: 287,489) BOINC@HOME CREDITS ABC 16613838.513356 | EINSTEIN 133913302.288695 ROSETTA 60333252.687309 | SETI 104587492.242787 -- 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/ All messages sent to this list will be publicly archived and cannot be deleted
