How to auto increase a monthly range, & sum formula based on date range criteria

Spiritseeker

New Member
Hi all,

I am hoping for help with two date formula queries I have.

I have built a spreadsheet which consultants at my work (a recruitment agency) list their successful job placements on. We record the placements monthly, and each month I simply copy and paste the previous month's 'template' (a 'block' of 60 rows) down into the area immediately below. The 'template' then has all the right formulae in the right place, one of which is a formula to auto increase the month by one. So if the previous month was March 2009, the next pasted 'block/template' will automatically be entitled April 2009. The placements are then summed up automatically per consultant per month with further formulae in the template that make calculations based on the data in the month's 'block'.

Some recent changes to the info we want to see per consultant per month leads me into my 1st question:
Instead of just listing March 2009, I now need (I think, unless Excel can be a bit clever) the date to be essentially the range of the whole month, ie 01/03/2009-31/03/2009 (I am in the UK), or for months with fewer days to cover the relevant period ie 01/02/2009-28/02/2009. Is there a way to do this? And let me explain why I think I need it in my 2nd question (the resolution to question 2 may mean my question 1 is not the best way of going about things).

I have a formula that totals the placement fees per consultant by using a sumproduct. The formula (in cell AM154) basically adds up a fee if the consultant's initials by the fee match the consultant's initials where I want the calculation to take place (cell A135). I now need to have a further if criteria, and the formula is getting a bit complicated, so I would appreciate some advice. The formula is as follows:

=SUMPRODUCT((I136:I189=AM154)*(K136:K189))

where the consultant's initials for the calculation are in cell AM154 and column I is where a consultant puts their initials in the data section. Column K is the placement fee.
I now need to modify the formula so that it checks the date an invoice will be raised for a placement for a certain month (ie I need to know the total value of invoices that will be raised in February for a specified consultant - placements are listed when they are made but we only invoice on the start date of a candidate and due to the nature of jobs a placement could be confirmed one month but the candidate may not start till the next month or even the month after).

So this is why I asked the first question. My modified calculation formula needs to check if the placement will be invoiced in the month range of the 'block' which will be copied & pasted down for the next month and so on. So it needs to sum any fee which matches the relevant initials, if the invoice date is with the date range of the month block that this formula is in. (eg if we are looking at the block for March 2009, for placements made by the consultant with initials MG, the calculaton needs to check the entire spreadsheet for fees associated with MG that will be invoiced in March 2009).

Column S is where consultants list the date for their placement to be invoiced. Column I is where they list their initials. Column K is where they list the fee value. My modified formula will have to look at the total columns rather than just the 60 rows in each 'block' because, as mentioned earlier, placements made one month can be invoiced in later months. Can someone help me with the modification of the formula? And also how to create the self calculating month formula which heads up each 'block' (I currently use

=DATE(YEAR(A67), MONTH(A67)+1, DAY(A67)) where A67 is the previous month in the previous 'block' in the format 01/03/2009.

If it can't be done in one cell, I am happy to use my existing formula on two separate cells as long as the calculation formula can check a date range between a date in one cell and a date in another cell - and as long as the different number of days in each month can be handled by excel somehow? Ie having 01/01/2009 in cell A1 and 31/01/2009 in cell B1, using my above formula would produce 01/02/2009 in cell A2 but 03/03/2009 in B2 - I need it to be 28/02/2009.

sorry for the length of my post, it's hard to explain concisely!

I want to be able to simply copy the block for a new month and the whole spreadsheet is self calculating, apart from cell A67 which is where I need to enter the starting point for the dates manually.

Can anyone tell me what formula I need to put in AN154 and how to do the auto month range increase problem? Thanks in advance. <!-- / message --><!-- attachments -->

Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Ok, I created a very simple version of my spreadsheet to produce a html sheet. A11 is now where I want to auto add the month as a range of the days within the month. J5 is where I want to have the sumproduct formula which will sum any placement on the spreadsheet which will be invoiced in the month located in cell A11. The result should therefore be 2000 as the other fee for 2500 is to be invoiced in April rather than March.<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis...[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

Last edited:
I'm not sure why my previous post looks a bit odd, sorry!

I tried your suggestion re the month formula, but it returns the error: #NAME?
Maybe the format of my source cell is not right? It's 01/03/2009. =EOMONTH(A4, 1) then returns the above error. Should my source for the formula be in another format?

You need to install the Analysis Toolpak from Tools > AddIns.

Just realised I mentioned the wrong cell in the post with the html sheet.

J5 is where I want to have the sumproduct formula which will sum any placement on the spreadsheet which will be invoiced in the month located in cell A4 (not A11).

I am no good with array formulae, someone provided me with that formula some time ago. So unfortunately I don't know how to add in a further criteria. But based on the existing formula:

=SUMPRODUCT((D5:D6=I5)*(F5:F6))

D5:D6 needs to change to be the entire D column (when I try this it insists on being two columns, how do I get around this?), as does column F. So the above formula checks that the initials in column D match those in cell I5, then the part that is missing is that I want to to also check that the date in column G falls within the month in cell A4, and if it does fall in that month and does match the initials, it needs to sum the values in column F. Obviously I need to have the right formula/format in cell A4 for it to be the whole month as the range to be checked against, if that makes sense?

Just realised I mentioned the wrong cell in the post with the html sheet.

J5 is where I want to have the sumproduct formula which will sum any placement on the spreadsheet which will be invoiced in the month located in cell A4 (not A11).

I am no good with array formulae, someone provided me with that formula some time ago. So unfortunately I don't know how to add in a further criteria. But based on the existing formula:

=SUMPRODUCT((D5:D6=I5)*(F5:F6))

D5:D6 needs to change to be the entire D column (when I try this it insists on being two columns, how do I get around this?), as does column F. So the above formula checks that the initials in column D match those in cell I5, then the part that is missing is that I want to to also check that the date in column G falls within the month in cell A4, and if it does fall in that month and does match the initials, it needs to sum the values in column F. Obviously I need to have the right formula/format in cell A4 for it to be the whole month as the range to be checked against, if that makes sense?

you cannot use entire column as ranges in sumproduct
Try:
=sumproduct(--(D2:D1000=I5),--(month(F2:F1000)=month(A4)),F2:F1000)

Replies
10
Views
217
Replies
9
Views
349
Replies
3
Views
296
Replies
0
Views
127
Replies
3
Views
134

1,203,396
Messages
6,055,163
Members
444,767
Latest member
bryandaniel5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back