| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM IF question (daily rainfall totals)
I have two colums of data, the first is a time date stamp (mm/dd/yyy hh:mm)
and the second is a rainfall total for each entry (0.00). I need to get a daily rainfall total. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM IF question (daily rainfall totals)
Add a column that returns the day (e.g. =Day(yourdatetimecell)) then SumIf
based on that column. You can, obviously, hide that column. "RedDixieCup" wrote: I have two colums of data, the first is a time date stamp (mm/dd/yyy hh:mm) and the second is a rainfall total for each entry (0.00). I need to get a daily rainfall total. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM IF question (daily rainfall totals)
Try this...
Dates/times in the range A2:A20 Values to sum in the range B2:B20 D2:Dn = unique dates D2 = 1/1/2009 D3 = 1/2/2009 D4 = 1/3/2009 etc etc Enter this formula in E2 and copy down as needed: =SUMPRODUCT(--(INT(A$2:A$20)=D2),B$2:B$20) -- Biff Microsoft Excel MVP "RedDixieCup" wrote in message ... I have two colums of data, the first is a time date stamp (mm/dd/yyy hh:mm) and the second is a rainfall total for each entry (0.00). I need to get a daily rainfall total. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM IF question (daily rainfall totals)
I can't get the unique day to come up?
"T. Valko" wrote: Try this... Dates/times in the range A2:A20 Values to sum in the range B2:B20 D2:Dn = unique dates D2 = 1/1/2009 D3 = 1/2/2009 D4 = 1/3/2009 etc etc Enter this formula in E2 and copy down as needed: =SUMPRODUCT(--(INT(A$2:A$20)=D2),B$2:B$20) -- Biff Microsoft Excel MVP "RedDixieCup" wrote in message ... I have two colums of data, the first is a time date stamp (mm/dd/yyy hh:mm) and the second is a rainfall total for each entry (0.00). I need to get a daily rainfall total. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM IF question (daily rainfall totals)
You either have to list them manually (not real difficult if they're
consecutive dates) or you need to use a helper column to extract the date portion then extract the uniques. I'm guessing you have data for every day of a time span. Just enter the first date in D2 then drag down the column and the consecutive dates will fill-in. -- Biff Microsoft Excel MVP "RedDixieCup" wrote in message ... I can't get the unique day to come up? "T. Valko" wrote: Try this... Dates/times in the range A2:A20 Values to sum in the range B2:B20 D2:Dn = unique dates D2 = 1/1/2009 D3 = 1/2/2009 D4 = 1/3/2009 etc etc Enter this formula in E2 and copy down as needed: =SUMPRODUCT(--(INT(A$2:A$20)=D2),B$2:B$20) -- Biff Microsoft Excel MVP "RedDixieCup" wrote in message ... I have two colums of data, the first is a time date stamp (mm/dd/yyy hh:mm) and the second is a rainfall total for each entry (0.00). I need to get a daily rainfall total. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM IF question (daily rainfall totals)
yeah I have five years of data
"T. Valko" wrote: You either have to list them manually (not real difficult if they're consecutive dates) or you need to use a helper column to extract the date portion then extract the uniques. I'm guessing you have data for every day of a time span. Just enter the first date in D2 then drag down the column and the consecutive dates will fill-in. -- Biff Microsoft Excel MVP "RedDixieCup" wrote in message ... I can't get the unique day to come up? "T. Valko" wrote: Try this... Dates/times in the range A2:A20 Values to sum in the range B2:B20 D2:Dn = unique dates D2 = 1/1/2009 D3 = 1/2/2009 D4 = 1/3/2009 etc etc Enter this formula in E2 and copy down as needed: =SUMPRODUCT(--(INT(A$2:A$20)=D2),B$2:B$20) -- Biff Microsoft Excel MVP "RedDixieCup" wrote in message ... I have two colums of data, the first is a time date stamp (mm/dd/yyy hh:mm) and the second is a rainfall total for each entry (0.00). I need to get a daily rainfall total. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM IF question (daily rainfall totals)
Anyone else? Still haven't gotten it yet.
I should probably mention I have a value for every 15 minutes. "RedDixieCup" wrote: yeah I have five years of data "T. Valko" wrote: You either have to list them manually (not real difficult if they're consecutive dates) or you need to use a helper column to extract the date portion then extract the uniques. I'm guessing you have data for every day of a time span. Just enter the first date in D2 then drag down the column and the consecutive dates will fill-in. -- Biff Microsoft Excel MVP "RedDixieCup" wrote in message ... I can't get the unique day to come up? "T. Valko" wrote: Try this... Dates/times in the range A2:A20 Values to sum in the range B2:B20 D2:Dn = unique dates D2 = 1/1/2009 D3 = 1/2/2009 D4 = 1/3/2009 etc etc Enter this formula in E2 and copy down as needed: =SUMPRODUCT(--(INT(A$2:A$20)=D2),B$2:B$20) -- Biff Microsoft Excel MVP "RedDixieCup" wrote in message ... I have two colums of data, the first is a time date stamp (mm/dd/yyy hh:mm) and the second is a rainfall total for each entry (0.00). I need to get a daily rainfall total. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM IF question (daily rainfall totals)
Hi,
Try a pivot table! Suppose your date-time info is in column A and the rainfall in column B. 1. Highlight the range and choose Data, PivotTable & PivotChart Report, Next, Next, Finish 2. Drag the date field to the Row area and the Rainfall field to the Data Area. 3. Click in the row area and choose Pivot Table, Group & show detail, Group, uncheck Month and check Day and click OK. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "RedDixieCup" wrote: yeah I have five years of data "T. Valko" wrote: You either have to list them manually (not real difficult if they're consecutive dates) or you need to use a helper column to extract the date portion then extract the uniques. I'm guessing you have data for every day of a time span. Just enter the first date in D2 then drag down the column and the consecutive dates will fill-in. -- Biff Microsoft Excel MVP "RedDixieCup" wrote in message ... I can't get the unique day to come up? "T. Valko" wrote: Try this... Dates/times in the range A2:A20 Values to sum in the range B2:B20 D2:Dn = unique dates D2 = 1/1/2009 D3 = 1/2/2009 D4 = 1/3/2009 etc etc Enter this formula in E2 and copy down as needed: =SUMPRODUCT(--(INT(A$2:A$20)=D2),B$2:B$20) -- Biff Microsoft Excel MVP "RedDixieCup" wrote in message ... I have two colums of data, the first is a time date stamp (mm/dd/yyy hh:mm) and the second is a rainfall total for each entry (0.00). I need to get a daily rainfall total. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM IF question (daily rainfall totals)
is that Excel2007?
"Shane Devenshire" wrote: Hi, Try a pivot table! Suppose your date-time info is in column A and the rainfall in column B. 1. Highlight the range and choose Data, PivotTable & PivotChart Report, Next, Next, Finish 2. Drag the date field to the Row area and the Rainfall field to the Data Area. 3. Click in the row area and choose Pivot Table, Group & show detail, Group, uncheck Month and check Day and click OK. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "RedDixieCup" wrote: yeah I have five years of data "T. Valko" wrote: You either have to list them manually (not real difficult if they're consecutive dates) or you need to use a helper column to extract the date portion then extract the uniques. I'm guessing you have data for every day of a time span. Just enter the first date in D2 then drag down the column and the consecutive dates will fill-in. -- Biff Microsoft Excel MVP "RedDixieCup" wrote in message ... I can't get the unique day to come up? "T. Valko" wrote: Try this... Dates/times in the range A2:A20 Values to sum in the range B2:B20 D2:Dn = unique dates D2 = 1/1/2009 D3 = 1/2/2009 D4 = 1/3/2009 etc etc Enter this formula in E2 and copy down as needed: =SUMPRODUCT(--(INT(A$2:A$20)=D2),B$2:B$20) -- Biff Microsoft Excel MVP "RedDixieCup" wrote in message ... I have two colums of data, the first is a time date stamp (mm/dd/yyy hh:mm) and the second is a rainfall total for each entry (0.00). I need to get a daily rainfall total. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM IF question (daily rainfall totals)
Shane's instructions were for XL2003, because you didn't specify what
version you were using. You can save everyone, including yourself, a lot of time by identifying your version right from the start. In 2007, Pivot Tables are on the Insert ribbon. Regards, Fred "RedDixieCup" wrote in message ... is that Excel2007? "Shane Devenshire" wrote: Hi, Try a pivot table! Suppose your date-time info is in column A and the rainfall in column B. 1. Highlight the range and choose Data, PivotTable & PivotChart Report, Next, Next, Finish 2. Drag the date field to the Row area and the Rainfall field to the Data Area. 3. Click in the row area and choose Pivot Table, Group & show detail, Group, uncheck Month and check Day and click OK. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "RedDixieCup" wrote: yeah I have five years of data "T. Valko" wrote: You either have to list them manually (not real difficult if they're consecutive dates) or you need to use a helper column to extract the date portion then extract the uniques. I'm guessing you have data for every day of a time span. Just enter the first date in D2 then drag down the column and the consecutive dates will fill-in. -- Biff Microsoft Excel MVP "RedDixieCup" wrote in message ... I can't get the unique day to come up? "T. Valko" wrote: Try this... Dates/times in the range A2:A20 Values to sum in the range B2:B20 D2:Dn = unique dates D2 = 1/1/2009 D3 = 1/2/2009 D4 = 1/3/2009 etc etc Enter this formula in E2 and copy down as needed: =SUMPRODUCT(--(INT(A$2:A$20)=D2),B$2:B$20) -- Biff Microsoft Excel MVP "RedDixieCup" wrote in message ... I have two colums of data, the first is a time date stamp (mm/dd/yyy hh:mm) and the second is a rainfall total for each entry (0.00). I need to get a daily rainfall total. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I sort a spreadsheet of daily events into weekly totals? | Excel Worksheet Functions | |||
| Daily Totals on a summary sheet | Excel Worksheet Functions | |||
| Collecting weekly and monthly totals from daily data | Excel Worksheet Functions | |||
| How can I calculate weekly totals of daily data in Excel | Excel Worksheet Functions | |||
| What formula can I use to get daily totals from list | Excel Worksheet Functions | |||
Linear Mode
