Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif by month and year | Excel Worksheet Functions | |||
sumproduct by year?? | Excel Discussion (Misc queries) | |||
sumif for months and year | Excel Worksheet Functions | |||
sumproduct for month and year | Excel Discussion (Misc queries) | |||
sumproduct in a given year | Excel Worksheet Functions |