use sumif / Sumproduct for year data
 

Hi
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
to
31 Dec 2100 20 2100

I have manged to get the following to work
=SUMPRODUCT((TEXT(C3:C36526,"yyyy")="2001")*(B3:B3 6526)) 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
Chris

use sumif / Sumproduct for year data
 

Hi,

have you tried a Pivot Table ?
Group by moths and years and place the month field before the year
field

Chris26;377253 Wrote:
Hi
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
to
31 Dec 2100 20
2100

I have manged to get the following to work
=SUMPRODUCT((TEXT(C3:C36526,"yyyy")="2001")*(B3:B3 6526)) 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
Chris


--
Pecoflyer

Cheers -
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105569

use sumif / Sumproduct for year data
 

Hi,

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

=SUMPRODUCT((YEAR(A1:A20)=2001)*(B1:B20))

or using a cell for the year

=SUMPRODUCT((YEAR(A1:A20)=E1)*(B1:B20))

where E1 contains 2001 or whatever year you want.

Mike

"Chris26" wrote:

Hi
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
to
31 Dec 2100 20 2100

I have manged to get the following to work
=SUMPRODUCT((TEXT(C3:C36526,"yyyy")="2001")*(B3:B3 6526)) 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
Chris

use sumif / Sumproduct for year data
 

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.

"Chris26" wrote:

Hi
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
to
31 Dec 2100 20 2100

I have manged to get the following to work
=SUMPRODUCT((TEXT(C3:C36526,"yyyy")="2001")*(B3:B3 6526)) 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
Chris

use sumif / Sumproduct for year data
 

Chris

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

=SUMPRODUCT((TEXT(A3:A36526,"mm")="01")*(YEAR(A3:A 36526)=2000)*(YEAR(A3:A36526)<=2010)*(B3:B36526))

Change as per your requirement

If this post helps click Yes
---------------
Jacob Skaria


"Chris26" wrote:

Hi
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
to
31 Dec 2100 20 2100

I have manged to get the following to work
=SUMPRODUCT((TEXT(C3:C36526,"yyyy")="2001")*(B3:B3 6526)) 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
Chris

use sumif / Sumproduct for year data
 

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)

=SUMPRODUCT((TEXT(A3:A36526,"mm")="01")*(YEAR(A3:A 36526)=2000)*(YEAR(A3:A36526)<=2010)*(B3:B36526))


If this post helps click Yes
---------------
Jacob Skaria


"Chris26" wrote:

Hi
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
to
31 Dec 2100 20 2100

I have manged to get the following to work
=SUMPRODUCT((TEXT(C3:C36526,"yyyy")="2001")*(B3:B3 6526)) 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
Chris