SUM IF question (daily rainfall totals)

[RedDixieCup]

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.

 

[BobT]

Add a column that returns the day (e.g. =Day(yourdatetimecell)) then SumIf
based on that column. You can, obviously, hide that column.

 

[T. Valko]

Try this...

Dates/times in the range A2:A20
Values to sum in the range B2:B20

D2n = 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)

 

[RedDixieCup]

I can't get the unique day to come up?

Try this...

Dates/times in the range A2:A20
Values to sum in the range B2:B20

D2n = 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)

 

[T. Valko]

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.

 

[RedDixieCup]

yeah I have five years of data

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.

 

[RedDixieCup]

Anyone else? Still haven't gotten it yet.

I should probably mention I have a value for every 15 minutes.

 

[Shane Devenshire]

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.

 

[RedDixieCup]

is that Excel2007?

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

 

[Fred Smith]

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