Add a between 2 dates to an IF statement

[myobreportguru]

I have the following formula and I would like to add a between 2 dates to the IF AND statement.

=IFERROR(INDEX('Weekly Income'!$E$3:$E$50000,SMALL(IF(('Weekly Income'!$B$3:$B$50000=$F$9+0)*('Weekly Income'!$C$3:$C$50000=$F$7),ROW('Weekly Income'!$A$3:$A$50000)-ROW('Weekly Income'!$A$3)+1,""),ROWS($A$3:A3))),"")

The formula is in D16 and the 2 dates are in E2 and E4. I have attached a cut out of what the spreadsheet looks like.

[myobreportguru]

I thought I would upload the file so where the data comes from can be seen.

[Glenn Kennedy]

Are you wanting it to look at the DUE date or the PAID date?

[Glenn Kennedy]

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

However, nothing on this Forum works quite as well as you might expect. The attachment icon doesn't work!! (Helpful, isn't it!!). Instead, just before you submit your post, click on GO ADVANCED (near the bottom) and then scroll down to Manage Attachments to open the upload window. The relevant instructions are at the top of that screen.

[myobreportguru]

I would like all the data to be filtered according to the IF statements. Some data comes from the Weekly Archives worksheet and some from Weekly Income worksheet. I'm not real good at some of these formulas so I hope it can be done.

[Glenn Kennedy]

That's not what I asked. In your main data, you have two columns with dates in them. The start and end dates are to apply to WHICH OF THOSE TWO COLUMNS?

[myobreportguru]

ok make it Rent Due column.

[Glenn Kennedy]

I misunderstood you. Apologies. Please upload an unprotected sheet.

[myobreportguru]

sorry I didn't think it was protected. I'll check it and get back to you tomorrow. It's late here.

[Glenn Kennedy]

OK. I got it figured. It wasn't protected. i was just being thick.

The problem was that your cells linked to you control object (the date select boxes) were returning the ROW NUMBER not the value. Instead, I used regular data validation to populate the DD boxes.

Please Login or Register  to view this content.

entered as an array. Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[Glenn Kennedy]

it just occurred to me how you can continue to use your original set up:

Please Login or Register  to view this content.

[myobreportguru]

hello Glenn. I have moved the layout around a bit and I want to produce a report that doesn't have the date range in it but still used for the calculation. So I will just adjust the print layout when I have the report ready. I have entered your formula in F16, is that where you meant this formula to be? I haven't changed any other formulas on the page yet so they are as I did them originally but I will change them all when I get it all working correctly. Can you check this for me please? I will upload a fresh copy of the file. Thanks.

[Glenn Kennedy]

OK. You have changed the properties of your combobox since the last version of this. It is now putting the date (not the row number of the date), formatted as text, in the linked cell. So there is now no need to use INDEX to refer to the start and end dates. You now just need to refer to D2 and D4 and add zero. In the INDEX part of the formula you had changed my whole column reference to match the range. Don't. Leave it as a whole column reference; otherwise it will give you an incorrect result.

Please Login or Register  to view this content.

Final working version is in the green cells, starting at F13, array entered.


I haven't a clue what you mean by "I want to produce a report that doesn't have the date range in it but still used for the calculation".

[myobreportguru]

yes yes yes thankyou Glenn. I've still got a bit of building to go but I think I've got it. By producing a report I meant just selecting the print range to exclude the date range. I won't put solved yet, I'll come back after I've done a bit more work. Thanks again.

[myobreportguru]

Hi Glenn

I have uploaded my latest version of this file. It's a little bit confusing the names are written differently on the 2 worksheets for the same people so I had to create a dropdown list from each worksheet to use with the formulas for data from each worksheet. However could you check the first formula in each column, I'm not sure about the row and rows part of the formula in each column. I've got a fair bit to learn yet.

[Glenn Kennedy]

Here it is, slightly changed.

ROW should cover the same range as the criteria-based matches (i.e. 2 to 50000). It returns the row number that matches your criteria. SMALL and a counter help to return the actual values in the order in which they appear.

ROWS is the counter. I normally set the first line to be ROWS($1:1) - which will return 1, copying down gives ROWS($1:2) - which gives 2, etc. Others prefer to start the counter in the cell in which the result is generated. So in cell F14, they'd have ROWS($F$14:F14).

[myobreportguru]

Thank you Glenn. I'm extremely grateful for your help.

[Glenn Kennedy]

You're welcome and thanks for the Reputation.