Adding Static Beginning Date to Formula

[tdsmith14]

For the formula shown below, currently the beginning and ending dates are referenced from are a vlookup located in cell AH1(2/1/2014) and AH2(2/28/2014). I’m trying to figure out how to make the beginning date static to start on 01/01/2014 and the ending date say the same, as this is for a report that is produced each month but on a year to date basis.

=IFERROR(SUMIFS('Appraisal Services Detail'!$BG:$BG,'Appraisal Services Detail'!$AI:$AI,">="&Beginning_Date,'Appraisal Services Detail'!$AI:$AI,"<="&Ending_Date,'Appraisal Services Detail'!$H:$H,"Residential",'Appraisal Services Detail'!$M:$M,A4),+SUMIFS('Appraisal Services Detail'!$BI:$BI,'Appraisal Services Detail'!$AO:$AO,">="&Beginning_Date,'Appraisal Services Detail'!$AO:$AO,"<="&Ending_Date,'Appraisal Services Detail'!$H:$H,"Residential",'Appraisal Services Detail'!$M:$M,A4))

[tdsmith14]

Sorr copied wrong formula, this is the correct formula in which I need to add Beginning and Ending date range.

[tdsmith14]

=IFERROR(SUMIFS('Appraisal Services Detail'!$BG:$BG,'Appraisal Services Detail'!$BF:$BF,2014,'Appraisal Services Detail'!$M:$M,$A4),+SUMIFS('Appraisal Services Detail'!$BI:$BI,'Appraisal Services Detail'!$BH:$BH,2014,'Appraisal Services Detail'!$M:$M,$A4))

[tdsmith14]

Pleae see attached for more detail.

[tdsmith14]

Is anyone available to assist me?

[Jonmo1]

If this is how you do a beginning date using a cell reference (or named range)
'Appraisal Services Detail'!$AI:$AI,">="&Beginning_Date

Then to make that static, it would be
'Appraisal Services Detail'!$AI:$AI,">=2/1/2014"

[tdsmith14]

I tried it with the date and using named cell but no luck. Below is what I have using what you suggested.

=IFERROR(SUMIFS('Appraisal Services Detail'!$BG:$BG,'Appraisal Services Detail'!$BF:$BF,2014,'Appraisal Services Detail'!$M:$M,$A4,'Appraisal Services Detail'!$BF:$BF,">=1/1/2014",'Appraisal Services Detail'!$BF:$BF,"<="&Ending_Date), +SUMIFS('Appraisal Services Detail'!$BI:$BI,'Appraisal Services Detail'!$BH:$BH,2014,'Appraisal Services Detail'!$M:$M,$A4))

[Pete_UK]

Is anyone available to assist me?

I'm not sure why you went to the trouble of taking a snapshot of part of your workbook, then pasted it into a Word document, then saved it, then attached it to your post. Why not attach the workbook itself, then we can see for ourselves what is in the cells that the formula refers to?

Pete

[Pete_UK]

You don't need this condition in there:

'Appraisal Services Detail'!$BF:$BF,2014

It looks like column BF contains dates, so they will never be equal to the number 2014.

Hope this helps.

Pete

EDIT: you have a similar condition in your second SUMIFS - change that to the starting date using the syntax already given.

Pete

[tdsmith14]

Hello Pete,

This a report related to my job and for confidentially reasons cannot disclose our clients name is why I did a screen shot, but give me a moment I may have an idea to submitting an excel copy.

[Pete_UK]

Don't bother - see my later post (#9).

Pete

[tdsmith14]

That will not work the year only will not work as I need ytd to always pull starting with 1/1/2014.

[Pete_UK]

Yes, but your formula in post #7 is trying to say that you want column BF to be greater than or equal to 1/1/2014 AND less than or equal to the end_date, so there is no need to also try to check for the year (and it won't work as you have written it, anyway).

Pete

[tdsmith14]

I'm not sure I understand what you are explaining. I'm trying to get the formula to start calculating from date 01/01/2014 specifically because there are other years listed in that same column (BF for instance).

[Jonmo1]

OK, thanks for the book.

Now which formula (column S, T, X, Y ??) Do you want to add a beginning date criteria to?
And which column contains the dates you want to test for a beginning date?
And what date is that?

[tdsmith14]

I'm sorry for not giving that information before hand. I would like to focus on cell Y4 (bolded red text). The column that contains the dates that I want to test is column BF on the "Appraisal Services Detail" tab, like mention in the formula in cell Y4. Where I am getting stuck is the part I've bolded below. The "Ending_Date" part of the formula is fine, just trying to create a start date of 01/01/2014. Each month when I create this report I will select the month that I am report, in this case month February, which will then give me the date range of 2/1/2014 to 2/28/2014. But I need to always include previous months (January 2014) reported as well the current month (February 2014). If you filter that data on the "Appraisal Services Detail" tab you'll see that the total would include March 2014, which I do not want on this report. By me using a greater than or equal to 01/01/2014 as the start date I will always have previously reported months included in my report each month. I can't use the "Beginning_Date" in cell AH because each month that date will change. I hope I didn't confuse you. Let me know if you have any questions.

=IFERROR(SUMIFS('Appraisal Services Detail'!$BG:$BG,'Appraisal Services Detail'!$BF:$BF,2014,'Appraisal Services Detail'!$M:$M,$A4,'Appraisal Services Detail'!$BF:$BF,">=",'Appraisal Services Detail'!$BF:$BF,"<="&Ending_Date), +SUMIFS('Appraisal Services Detail'!$BI:$BI,'Appraisal Services Detail'!$BH:$BH,2014,'Appraisal Services Detail'!$M:$M,$A4))

[Jonmo1]

Column BF on the Appraisal Services Detail Tab does not contain a Date.
It contains only a Year # (2013 or 2014 etc)

Maybe that is what is tripping you up.

However, column BF is a formula that is extracting the year from a date in column AI
Perhaps your formula should refer to column AI for the >=begindate criteria

=IFERROR(SUMIFS('Appraisal Services Detail'!$BG:$BG,'Appraisal Services Detail'!$BF:$BF,2014,'Appraisal Services Detail'!$M:$M,$A4,'Appraisal Services Detail'!$AI:$AI,">=2/1/2014",'Appraisal Services Detail'!$AI:$AI,"<="&Ending_Date), +SUMIFS('Appraisal Services Detail'!$BI:$BI,'Appraisal Services Detail'!$BH:$BH,2014,'Appraisal Services Detail'!$M:$M,$A4))

[tdsmith14]

Hello Jonmo1,

Sorry for the late reply as I had clocked out for the day yesterday, but you are so right about me referencing the wrong column. I had been staring at this dern formula all day yesterday trying my best to figure this out that I must have gotten my columns mixed up. I tried the formula out and it worked perfectly. Gave me the excact figure I needed. Thank you SO MUCH for your help, I really appreciate it. I'm learning this formula thing little by little. This was a great learning experience for me.


Sheka

[Jonmo1]

You're welcome.

[Pete_UK]

This was a great learning experience for me.

Yeah, and it wasted quite a bit of other people's time just because your cell references were wrong - how many others did you PM (twice) to get them hooked in?

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

And you should by now know about directly thanking those who have tried to help you by clicking on the small "star" icon located in the lower left corner of their post(s).

Pete

[tdsmith14]

To: Pete_UK

I don't appreciate your tone. I do appreciate your the time and effort you put into trying to assist me with this formula, even through my mistake in referencing the wrong cells, as this was UNINTENTIONAL. I am not an expert at this kind of stuff, which is I reached out for a little help, and I PM you because you were such a great help on previous post of mine, I figured you wouldn't mind helping me again.

tdsmith14