Summing values based on calendar dates
 

I have an aging report, and I have a single row of sequential dates that span
six months, and its is a dynamic row based on the date I type in the first
cell. Below the row are dollars. How does one compose a formula to
automatically sum 31 columns if it a month is 31 days, 30 columns for a 30
day month, or 28 columns for 28 days for a February?

Thank you.

Summing values based on calendar dates
 

Adjust these ranges to cover your data, adjust the MONTH reference to the
month you want. In this example, it's 2 for February:

=SUMPRODUCT(--(MONTH($A$1:$DB$1)=2),A2:DB2)

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Adrian1962" wrote:

I have an aging report, and I have a single row of sequential dates that span
six months, and its is a dynamic row based on the date I type in the first
cell. Below the row are dollars. How does one compose a formula to
automatically sum 31 columns if it a month is 31 days, 30 columns for a 30
day month, or 28 columns for 28 days for a February?

Thank you.

Summing values based on calendar dates
 

Sum will add 0 for cells that are empty. So have 31 columns and months that
have less than 31 days leave these cells blank. If you have a total column
make sure it is after the 31st column. So months that have less than 31 days
will have more blank columns between the last date column and the total column

"Adrian1962" wrote:

I have an aging report, and I have a single row of sequential dates that span
six months, and its is a dynamic row based on the date I type in the first
cell. Below the row are dollars. How does one compose a formula to
automatically sum 31 columns if it a month is 31 days, 30 columns for a 30
day month, or 28 columns for 28 days for a February?

Thank you.

Summing values based on calendar dates
 

Sorry - I don't understand the response the both of you provided. Maybe an
illustration can present more information on my part. I would like a formula
that sums the automatically for me the calendar period. For example below
are two periods: April and May. how can I have a formula that automatically
adds 30 columns for April and 31 columns for May. The dates are sequential.
I don't have the ability to insert a space between months.

Period
April $3,000
May $3,100

Calendar 4/1/2009 €¦..4/30/2009 5/1/2009...5/31/2009
Daily Amnt $100 $100 $100 $100
-------------------------------------------------------------------------

"Adrian1962" wrote:

I have an aging report, and I have a single row of sequential dates that span
six months, and its is a dynamic row based on the date I type in the first
cell. Below the row are dollars. How does one compose a formula to
automatically sum 31 columns if it a month is 31 days, 30 columns for a 30
day month, or 28 columns for 28 days for a February?

Thank you.

Summing values based on calendar dates
 

Let me explain how the suproduct formula works

=SUMPRODUCT(--(MONTH($A$1:$DB$1)=4),A2:DB2)

You have a header column with the dates. In the formula above that is the
range A1:DB1. So the formula tests if the header row contains the month
april (4). Row 2 (A2:DB2) contains the numbers you want to add. Note the
columns in the first row is the same as the columns in the second row. The
two -- converters the True or False to 1 or 0.

so what you initially get is

=SUMPRODUCT(--(TRUE,TRUE,TRUE,for 30 days, False,False,
Fale...)),(100,150,100,200,........))

then the true and false become 1 or 0

=SUMPRODUCT((1,1,1,...,1,0,0,0,....)*(100,150,100, 200,........))

Now the 1's and 0's are multiplied by the numbers you want the sum

=SUMPRODUCT(((100,150,100,200,..up to 30 ...,200,0,0,0,0,0,0,0,0,0,0,.....))

then sumproduct will sum the numbers that are left





"Adrian1962" wrote:

Sorry - I don't understand the response the both of you provided. Maybe an
illustration can present more information on my part. I would like a formula
that sums the automatically for me the calendar period. For example below
are two periods: April and May. how can I have a formula that automatically
adds 30 columns for April and 31 columns for May. The dates are sequential.
I don't have the ability to insert a space between months.

Period
April $3,000
May $3,100

Calendar 4/1/2009 €¦..4/30/2009 5/1/2009...5/31/2009
Daily Amnt $100 $100 $100 $100
-------------------------------------------------------------------------

"Adrian1962" wrote:

I have an aging report, and I have a single row of sequential dates that span
six months, and its is a dynamic row based on the date I type in the first
cell. Below the row are dollars. How does one compose a formula to
automatically sum 31 columns if it a month is 31 days, 30 columns for a 30
day month, or 28 columns for 28 days for a February?

Thank you.

Summing values based on calendar dates
 

Excel 2007 Pivot Table
None of those pesky formulas needed.
Automatic sorting and grouping.
New data is automatically included.
No specific date ordering is required.
http://www.mediafire.com/file/ml1wjtveruk/03_21_09.xlsx