# Formula For Sum of Spend Per Month During a Start and End Date

1. ## Formula For Sum of Spend Per Month During a Start and End Date

Hello All,

Many Thanks for looking at this problem!

Well, I have a database of spend per month using a sumif function. What I need is a formula that sums up the total spend between 2 dates and anything before or after these dates are not included in the total spend.

So for instance, a person spends 100 pounds a month for 24 months between dates 01/01/11 - 01/01/13. But I need a formula to work out what the person spent between dates 04/09/11 - 04/07/12. This is needed for over 5000 lines of data.

I hope iv explained that ok. Iv attached an example as well.

Many Thanks

Darren

here you go!

3. ## Re: Formula For Sum of Spend Per Month During a Start and End Date

RCM
Why is the concatenation needed in the formula below?

=SUMIFS(\$E3:\$AC3,\$E\$1:\$AC\$1,">="&\$B3,\$E\$1:\$AC\$1,"<="&\$C3)

4. ## Re: Formula For Sum of Spend Per Month During a Start and End Date

@K m:
that is needed in COUNTIF, SUMIF or AVERAGEIF when you are combining it with a cell reference. otherwise, it wont recognise it as a cell reference

@Darren:
i think your months in row 1 is always starting from 1st of the month. so is 04/09/11 - 04/07/12 excluding Sep-11 & Jul-12? if you were to include, are they supposed to be pro-rated?

5. ## Re: Formula For Sum of Spend Per Month During a Start and End Date

Sumifs

Formula:
`Please Login or Register  to view this content.`

6. ## Re: Formula For Sum of Spend Per Month During a Start and End Date

Thanks so much guys!

Benishiryo - Yes they are supposed to include those months.

How would i change that formula to include only certain cells. For example iv attached another spreadsheet which is similar to the one above but iv added two coloums to each month. But I only want to include the invoice total for that month.

On the attached spreadsheet, the amount under invoice total for each month is what i want to look at. That forumla by RCM is great but tis there a way to change to just accept them cells?

I hope you understaned.

Many Thanks

Darren

7. ## Re: Formula For Sum of Spend Per Month During a Start and End Date

Hi
You can use the SUMIFS.
Formula:
`Please Login or Register  to view this content.`

8. ## Re: Formula For Sum of Spend Per Month During a Start and End Date

Thanks Kevin!! That Works perfectly!

When I drag down that formula down for different data, the formula does not change. Am I doing something wrong?
So in the above example, for person 2, person 3 etc.

Cheers
Darren

9. ## Re: Formula For Sum of Spend Per Month During a Start and End Date

Cheers Guys! All Sorted.

Thanks
Darren

There are currently 1 users browsing this thread. (0 members and 1 guests)