Need look up of month to get total for the month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Column A has the date mm/dd/yyyy
Column B has the clients name
Coulmn C has the amount paid
Column D is January's total paid and E = Feb, F = March, etc.
I want in the month total columns (D - O) to display the total amount in
column C for each respective month.
So in D1 I want a formula that will look at the date (columnA) and add all
the rows that have January (01/01/2007) as the date added up and entered in
D1. I would copy the formula for each month.
Is there a way to do this? And if so, what is the syntax?
Thanks,
 
drumz said:
Column A has the date mm/dd/yyyy
Column B has the clients name
Coulmn C has the amount paid
Column D is January's total paid and E = Feb, F = March, etc.
I want in the month total columns (D - O) to display the total amount in
column C for each respective month.
So in D1 I want a formula that will look at the date (columnA) and add all
the rows that have January (01/01/2007) as the date added up and entered
in
D1. I would copy the formula for each month.
Is there a way to do this? And if so, what is the syntax?
Click to expand...

It looks like if you put this formula in D1...

=SUMPRODUCT((COLUMN(D1)-3=MONTH($A2:$A1000))*$C2:$C1000)

and copy it across to O1, it will do what you want. Now, to be clear, the
above is based on an assumption that any date within the month is a match
(not only January 1st as your example showed) and that all of your data is
for the same year (2007 only, not 2007 combined with any other year). By the
way, the two uses of 1000 allow for 1000 rows of data... you should change
them to a whatever number is equal to or larger than the most data you ever
expect to have.

Rick
 
Thanks Rick, it works perfectly!

Rick Rothstein (MVP - VB) said:
It looks like if you put this formula in D1...

=SUMPRODUCT((COLUMN(D1)-3=MONTH($A2:$A1000))*$C2:$C1000)

and copy it across to O1, it will do what you want. Now, to be clear, the
above is based on an assumption that any date within the month is a match
(not only January 1st as your example showed) and that all of your data is
for the same year (2007 only, not 2007 combined with any other year). By the
way, the two uses of 1000 allow for 1000 rows of data... you should change
them to a whatever number is equal to or larger than the most data you ever
expect to have.

Rick
Click to expand...
 
Back
Top