Sum if with date range

[italy16]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Contract End Date[/TD]
[TD]amounts[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 500"]
<tbody>[TR]
[TD]1/2/14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]2/5/14[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]4/6/14[/TD]
[TD]70[/TD]
[/TR]
</tbody>[/TABLE]

Hello - I'm trying a sumif (I think) where I need to sum the "amounts" column if the contract end date is within a certain number of months from a beginning date. For example, how would I sum the "amounts" column if I wanted all contracts that started within 2 months of Jan. 1 2014? It should sum the 50+60 since they are in the range but not 4/6/14 since it is out of the 2 months window.

Thanks very much

 

[Johnny C]

If you have the start date in a cell say C1, you would use

=SUMIFS($B$2:$B$500,$A$2:$A$500,"<="&DATE(YEAR($C$1),MONTHS($C$1)+2,DAY($C$1))

I'm assuming the dates above are US format MM/DD/YY
Also, SUMIF and SUMIFS have different order of parameters, it's a good idea to ignore SUMIF and stick to SUMIFS, even if there's only 1 condition, in case you need to add conditions in the future.
You need to tweak the <= as needed to include or exclude contracts that finished before or on the date in C1

I've assumed your dates are in col A and amounts in col B. Also only rows down to 500, you need to adjust the references as needed

 

[italy16]

Thank you. I'm getting a missing parentheses error (I'm using a Mac) - I can never figure out the parenthese thing . Any ideas? Thanks in advance.

=sumifs(N14:N16,O14:O16,"<="&DATE(year(N12),months(N12)+2,day(N12))

 

[Johnny C]

You just need one on the end. Usually Excel suggests one but it didn't on this occasion, sometimes it doesn't it there's lots of groups of brackets. I don't think using a Mac is the problem, it's just Excel.
=sumifs(N14:N16,O14:O16,"<="&DATE(year(N12),months(N12)+2,day(N12)))

 

[italy16]

Thank you. Yea, I've noticed Mac doesn't add the parenthese like a PC does....thanks again.