# Adding a date formula to an existing formula

1. ## Adding a date formula to an existing formula

Hi Folks,

I have an issue that is perplexing me.

I currently have the below formula:
=SUMPRODUCT(SUMIFS('Data - CURRENT'!\$K:\$K, 'Data - CURRENT'!\$R:\$R, Receipted_status, 'Data - CURRENT'!\$B:\$B, "Area 1"))
This just pulls in all data regardless of the period.

However, the data that I have is spanning a whole year. The data that I am reporting on is within a rolling 12 month period. I have two requests if I may be so bold:
1. I need to just report on the previous month and bring back a certain total figure - hence the above formula.
2. I need to report a breakdown of the previous 12 rolling months. This month report will be March 2013 to February 2014. Next month it will be April 2013 to March 2014 and so on.

For the first one, to bring back the previous month I would use: =TEXT(DATE(2014,IF(MONTH(TODAY())-1>0,MONTH(TODAY())-1,12),1),"MMMM") but not sure if I am barking up the wrong tree and how to add this to my original formula??

As I said...perplexing!

2. ## Re: Adding a date formula to an existing formula

Have you thought about using pivot tables?

3. ## Re: Adding a date formula to an existing formula

Hi,

Of course, my apologies for this. I have attached an example file. The dates should be based on column S. I am trying to stay away from Pivot Tables as this is only 1 formula of a multitude of formulas that work out different things.

Hope that you can help.

Andrew

4. ## Re: Adding a date formula to an existing formula

nice formula

Put this into sheet1 A3...
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

=SUMPRODUCT(SUMIFS('Data - CURRENT'!\$I:\$I, 'Data - CURRENT'!\$P:\$P,Receipted_status, 'Data - CURRENT'!\$A:\$A,\$A5,'Data - CURRENT'!\$S:\$S,"<"&Sheet1!\$A\$3,'Data - CURRENT'!\$S:\$S,">="&EDATE(Sheet1!\$A\$3,-1)))

Change the -1 (bold) to -12 for the 12-month requirement

5. ## Re: Adding a date formula to an existing formula

Hi Fdibbins

I sincerely apologise - I completely missed your response and I feel so bad. Silly emails. Anyway - thank you so much...this works a treat.

Much appreciated.
Andrew

6. ## Re: Adding a date formula to an existing formula

No problem, happy to help and thanks for the feedback

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1