Re: [libreoffice-users] COUNTIF / DAYS / ?

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:          hylton@conacher.co.za
To:                     LibreOffice Users <users@global.libreoffice.org>
From:                   "Hylton Conacher (ZR1HPC)" 
<hylton@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+unsubscribe@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:mikes@guam.net                            
  mailto:msetzerii@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+unsubscribe@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