COUNTIF / DAYS / ?

users
#1

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

#2

Hi,

If you have one row per day of the year, you can find out easily where
the row corresponding to "TODAY()-1" is located by finding out the
number of days between Jan 1st of that year and "yesterday". This would
be something like (you may need to adjust based on the heading of your
column(s)):

=DAYS(YEAR(TODAY()-1)-1;MONTH(TODAY()-1);DAY(TODAY()-1);YEAR(TODAY()-
1)-1;1;1)

The formula above calculates the number of days between yesterday last
year and Jan 1st of last year. Now, you need to find in which column to
add. Since you seem to have a heading providing the year, you can use
the MATCH() function to locate the column:

=MATCH(YEAR(TODAY()-1)-1;$a$1:$z$1;0)

This should give you the column where last year's data is located (I
used a1 to z1, but any row reference will work).

To add the values, you now just need to use a combination of SUM() and
OFFSET():

=SUM(OFFSET($A$2;0;MATCH(YEAR(TODAY()-1)-1;$a$1:$z$1;0)-
1;DAYS(YEAR(TODAY()-1)-1;MONTH(TODAY()-1);DAY(TODAY()-1);YEAR(TODAY()-
1)-1;1;1);1))

What that does:

OFFSET() allows you to create dynamic arrays for functions like SUM().
I used the anchor point $A$2 (I supposed your heading is in row 1), and
told OFFSET to move from that reference 0 rows down and
"MATCH(YEAR(TODAY()-1)-1;$a$1:$z$1;0)-1" columns to the right; then,
from that new point, make an array that is "DAYS(YEAR(TODAY()-1)-
1;MONTH(TODAY()-1);DAY(TODAY()-1);YEAR(TODAY()-1)-1;1;1)" rows high and
1 column wide. The resulting array is then used by SUM() to calculate
the total rainfall.

This will probably need to be adjusted a bit to take into account the
position of your column heading and to correctly find the row where to
stop, but you should now have something to play with.

I hope this helps.

Rémy Gauthier.

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

#3

Hi.
Is the rainfall data regular (every day) or sporadic.
If sporadic, is the amount of rainfall cumulative since the previous date.

I also take it that you want 2 outputs. The total amount of rainfall over the desired period and the number of data points on which rainfall was recorded over that period.

Steve

#4

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

Send reply to: hylton@conacher.co.za
<hylton@conacher.co.za>
Date sent: Sun, 7 May 2017 21:55:45 +0200

#5

Hi,

If you have one row per day of the year, you can find out easily where the row corresponding to "TODAY()-1" is located by finding out the number of days between Jan 1st of that year and "yesterday". This would be something like (you may need to adjust based on the heading of your column(s)):

=DAYS(YEAR(TODAY()-1)-1;MONTH(TODAY()-1);DAY(TODAY()-1);YEAR(TODAY()-1)-1;1;1)

The formula above calculates the number of days between yesterday last year and Jan 1st of last year. Now, you need to find in which column to add. Since you seem to have a heading providing the year, you can use the MATCH() function to locate the column:

=MATCH(YEAR(TODAY()-1)-1;$a$1:$z$1;0)

This should give you the column where last year's data is located (I used a1 to z1, but any row reference will work).

To add the values, you now just need to use a combination of SUM() and OFFSET():

=SUM(OFFSET($A$2;0;MATCH(YEAR(TODAY()-1)-1;$a$1:$z$1;0)-1;DAYS(YEAR(TODAY()-1)-1;MONTH(TODAY()-1);DAY(TODAY()-1);YEAR(TODAY()-1)-1;1;1);1))

What that does:

OFFSET() allows you to create dynamic arrays for functions like SUM(). I used the anchor point $A$2 (I supposed your heading is in row 1), and told OFFSET to move from that reference 0 rows down and "MATCH(YEAR(TODAY()-1)-1;$a$1:$z$1;0)-1" columns to the right; then, from that new point, make an array that is "DAYS(YEAR(TODAY()-1)-1;MONTH(TODAY()-1);DAY(TODAY()-1);YEAR(TODAY()-1)-1;1;1)" rows high and 1 column wide. The resulting array is then used by SUM() to calculate the total rainfall.

This will probably need to be adjusted a bit to take into account the position of your column heading and to correctly find the row where to stop, but you should now have something to play with.

I hope this helps.

Rémy Gauthier.

Thank you for the in-depth reply! I will need to play with this and the functions you mention to get it right but hopefully I will come right. I will post my final formula here, for anyone else wanting to do something similar.

Regards
Hylton

#6

Michael,

Thank-you for the time and response

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

Correct :slight_smile:

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)

This turns out to be a problem as I am working with five(5) years of data and whilst my example did not shot it there are multiple values under each month, even if it is zero.

I don't think five columns of dates is a smart way to do this.
I am rather going to investigate making my date column a rolling year numeric month/day for 365 rows, with the columns being divided into years.

Example:

Date 2016 2017 2018
0101 0 1 5
0102 1 0 0
0103 0 5 10
0501 1 0 0
0922 5 1 1
3112 0 1 0

I then reckon use of MATCH, DAYS, COUNTIF and SUM should deliver what I seek.

Your thoughts on how to get the rows in a column formatted as Date to show a sequential month and day number?

I have Bcc'd you as the message contain an attachment of my existing sheet.

Regards
Hylton