use sumif / Sumproduct for year data



I have a set of daily data (total rainfall) for100 years.
What would be the easiest way to extract the total rainfall for:-
January Year 2000-2100, February 2000-2100 etc
and also
Total rainfall for 2001, 2002 etc

Col A (Date-Days) Col B (Rainfall mm) Col C (Format to only
show year)
1 Jan 2001 0 2001
2 Jan 2001 6 2001
3 Jan 2001 4 2001
31 Dec 2100 20 2100

I have manged to get the following to work
=SUMPRODUCT((TEXT(C3:C36526,"yyyy")="2001")*(B3:B36526)) although is it
possible to change "2001" to a cell ref so dont have to go into each
individual line and cgange to "2002", "2003" etc

Many Thanks

Mike H


You dont need column C you can work directly on your dates


or using a cell for the year


where E1 contains 2001 or whatever year you want.


Sam Wilson

Put your years in column E, so E1 is 2000, E2 is 2001 etc

In cell F1 use the formula =SUMIF(C:C,E1,B:B) and then copy this down.

Jacob Skaria


It is almost the same approach. For the month january from 2000 to 2010
(both inclusive)


Change as per your requirement

If this post helps click Yes

Jacob Skaria

Hi Chris

The approach is again the same. You dont need a reference column C

The below will return the total for the month of January only from 2000 to
2010 (both inclusive)


If this post helps click Yes

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question