Summarising date-based data

[g&l]

Hi all,
Is there a way of using SUMIF & MATCH & COUNTIF to calculate monthly totals
and number of rain days from a continuous 62 year rainfall data set? As the
data is 22,600 rows, not having to scroll down to each monthly total is also
req'd.
The data looks like this:

Date Rainfall
01/01/1941 23
02/01/1941 18
03/01/1941 2
: :
: :
30/01/2003 5
31/01/2003 22

Thanks

 

[Dave Ramage]

Usually a Pivot Table would be the way to go, but there
are too many individual dates for that to work. As an
alternative, create a table of months in a spare column
(I'll assume its column E), looking like this:

Month Month Total Num Rain Days
01/01/1941
01/02/1942
..
..
01/01/2003

Now in the Month Total colum, enter this array formula:
=SUM(IF(DATE(YEAR($A$2:$A$22677),MONTH($A$2:$A$22677),1)
=E2,1,0)*B2:B22677)

Where: Date data is A2:A22677
Rainfall data is B2:B22677
Month is in colum E, starting in E2

For Num Rain Days, use this:
=SUM(IF(DATE(YEAR($A$2:$A$22677),MONTH($A$2:$A$22677),1)
=E2,1,0)*IF(B2:22677>0,1,0))

These are both array formulas, so hold Ctrl + Shift as you
enter them.

Cheers,
Dave.