Formula for counting rain days of the year, month, and or week

[dedark05]

Hi Guys,

I need to count the number of days it rained of a given time (year,month, and week).

I have rainfall data of 2 years with 30 mins data interval and I need to summarize the rain days of the year, the month of a given year, and the week of the given year (week 5 for example). I can't think of a formula to solve this since my data is in 30 mins interval not daily.

please see this link below to see my data sheet;
https://www.dropbox.com/s/p58ao5lh9e...days.xlsx?dl=0

or see attached.

Any help would be greatly appreciated.

Thanks,
DEAN

[FDibbins]

See if this works for you...
G6=COUNTIFS(C:C,">0",A:A,">="&DATE(F6,1,1),A:A,"<="&DATE(F6,12,31))
I6=COUNTIFS(C:C,">0",A:A,">="&DATEVALUE(1&H6),A:A,"<"&EDATE(DATEVALUE(1&H6),1))
for the week num, I used a helper that you can hide if needed...
D3=WEEKNUM(A3)&", "&YEAR(A3)
copied down

then I changed J6 etc to just 1, 2015
K6=COUNTIFS(C:C,">0",D:D,J6)

[humdingaling]

put it in a pivot table?

[dedark05]

See if this works for you...
G6=COUNTIFS(C:C,">0",A:A,">="&DATE(F6,1,1),A:A,"<="&DATE(F6,12,31))
I6=COUNTIFS(C:C,">0",A:A,">="&DATEVALUE(1&H6),A:A,"<"&EDATE(DATEVALUE(1&H6),1))
for the week num, I used a helper that you can hide if needed...
D3=WEEKNUM(A3)&", "&YEAR(A3)
copied down

then I changed J6 etc to just 1, 2015
K6=COUNTIFS(C:C,">0",D:D,J6)

Hi FDibbins,

It is not counting as days. I think it is counting the 30mins interval of the data not days.

for years, it is counting more than 365 days, for the month it is counting more than 31, and for the week more than 7.

Regards,
Dean

[dedark05]

put it in a pivot table?

Hi humdingaling,

I have been asked not to use PIVOT table.

Regards,
Dean

[FDibbins]

Hum, Im trying to put a PT together, as suggested, but...
1. how would you get week nums?
2. how do you exclude 0's?

	F	G	H	I	J	K	L	M	N
5	year	rain days	month	rain days	week	rain days		2015
6	2015	1733	January 2015	292	1, 2015	20		Jan	1485
7	2016	930	February 2015	175	2, 2015			Feb	1340
8			March 2015	146	3, 2015			Mar	1487
9			January 2016	224	wk. 4, 2015			Apr	1440
10			February 2016	252				May	1487
11			March 2016	105				Jun	1440
12								Jul	1488
13								Aug	1488
14								Sep	1440
15								Oct	1488
16								Nov	1440
17								Dec	1488
18								2016	6778

M:N are a PT

[humdingaling]

very well

using two helper columns

the bottom numbers was me checking the answers of which i had a pivot table method

[humdingaling]

Ford
with Pivot method you need a side helper for it

[FDibbins]

OK, kinda like what I used for the week/year

[humdingaling]

Reattached file with pivot table and helper

[dedark05]

Reattached file with pivot table and helper

FDibbins/humdingaling

Sorry if I did not explained it well. Anyway, This works! Thanks!

but with this so much volume of data and still counting, Is it possible to not have a helper? the actual file of the raw data is in separate excel file (should not be edited for some reason) from the formula for counting rain days summary.

[humdingaling]

you could just take the assumption that old data does not change

so use the raw to create a summary file once and update the summary file every month with only NEW data

[dedark05]

Reattached file with pivot table and helper

Hi humdingaling,

This is counting the raindays correctly but I can't see the reference date to count. how can I choose what year, month, or week to summarize rain days? for example @ K6 for week 1, 2015 rain days. K6=SUMPRODUCT(--(YEAR($A$3:$A$24291)=2015)*($D$3:$D$24291="Y")*(($E$3:$E$24291)=ROW(A1)))

what is A1? and I cant see the wk 1, 2015 cell reference in the formula

[humdingaling]

i was just making the formula update with the row

row(a1) = 1
row(A2) = 2

its not necessary and i actually think you should take it out as i have found this sheet run really slow due to the amount of data

[dedark05]

i was just making the formula update with the row

row(a1) = 1
row(A2) = 2

its not necessary and i actually think you should take it out as i have found this sheet run really slow due to the amount of data

I get it. I will just change raw(A1) to my reference (wk.1 ,20150) and create a formula that will make "wk.1 ,2015" equate to "1"
and "2015" at YEAR($A$3:$A$24291)=2015 that will "wk.1,2015" equate to "2015"

thanks for the immediate help, humdingaling.

[FDibbins]

SP in that amount of data will run slow

As hum suggested, you can "value" the formulas that pull put the week nums, and just leave the formulas there for the latest dates

(thanks for the rep

[AliGW]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).