SUM IF question (daily rainfall totals)

  • Thread starter Thread starter RedDixieCup
  • Start date Start date
R

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.
 
Add a column that returns the day (e.g. =Day(yourdatetimecell)) then SumIf
based on that column. You can, obviously, hide that column.
 
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)
 
I can't get the unique day to come up?

T. Valko said:
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)
Click to expand...
 
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.
 
yeah I have five years of data

T. Valko said:
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.
Click to expand...
 
Anyone else? Still haven't gotten it yet.

I should probably mention I have a value for every 15 minutes.
 
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.
 
is that Excel2007?

Shane Devenshire said:
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
Click to expand...
 
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
 
Back
Top