SUM IF question (daily rainfall totals)

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.
 
B

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

T. Valko

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)
 
R

RedDixieCup

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)
 
T

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.
 
R

RedDixieCup

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.
 
R

RedDixieCup

Anyone else? Still haven't gotten it yet.

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

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.
 
R

RedDixieCup

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
 
F

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top