sum based on date ranges

[Victtor]

2 column ranges
column AA=PayDate range
column AB=Wages range

What I have done is list all my bi-weekly pay dates for the next 3 years in column AA. The range is named 'PayDate'. I listed my net pay in column AB and this range is called 'Wages'.


I want to create a budget based on the number of pay dates are in a particular month. I get paid every 14 days. You can use 06/09/2011 as a starting paydate. Sometimes there are 2 paydates and sometimes there are 3 paydates that fall in a given month. I need either VB code or a formula to look in my PayDate range and return the total pay for a particular month.

In Cell B1 I have June, 2011; C1=July, 2011;D1=August, 2011 etc.


So,
In Cell B2, I need a total of all wages that fall in June, 2011
In Cell C2, I need a total of all wages that fall in July, 2011
In Cell D2, I need a total of all wages that fall in August, 2011
etc...

 

[texasalynn]

try this

Excel Workbook
	A	B
1	Date	Wages this month
2	Jun-2011	1,846.15
3	Jul-2011	1,846.15
4	Aug-2011	1,846.15
5	Sep-2011	2,769.23
6	Oct-2011	1,846.15
7	Nov-2011	1,846.15
8	Dec-2011	1,846.15
9	Jan-2012	1,846.15
10	Feb-2012	1,846.15
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(--(MONTH(PayDate)=MONTH(A2)),--(YEAR(PayDate)=YEAR(A2)),(Wages))

Excel Workbook
Name	Refers To
PayDate	=Sheet1!$AA$1:$AA$461
Wages	=Sheet1!$AB$1:$AB$461
Workbook Defined Names

 

[T. Valko]

2 column ranges
column AA=PayDate range
column AB=Wages range

What I have done is list all my bi-weekly pay dates for the next 3 years in column AA. The range is named 'PayDate'. I listed my net pay in column AB and this range is called 'Wages'.


I want to create a budget based on the number of pay dates are in a particular month. I get paid every 14 days. You can use 06/09/2011 as a starting paydate. Sometimes there are 2 paydates and sometimes there are 3 paydates that fall in a given month. I need either VB code or a formula to look in my PayDate range and return the total pay for a particular month.

In Cell B1 I have June, 2011; C1=July, 2011;D1=August, 2011 etc.


So,
In Cell B2, I need a total of all wages that fall in June, 2011
In Cell C2, I need a total of all wages that fall in July, 2011
In Cell D2, I need a total of all wages that fall in August, 2011
etc...

What version of Excel are you using?

In Cell B1 I have June, 2011; C1=July, 2011;D1=August, 2011 etc.

Are those dates formatted to display that way? If they're dates, what dates? 1st of the month dates?

 

[Victtor]

texaslynn, thanks this worked perfectly!

 

[texasalynn]

Your welcome and thanks for the feedback