Forum Discussion
Look up values equal or less than 3 based in a specific month
Hello I am looking for a formula to calculate how many records in a month are equal to or under a certain value? For example: I have a list of site visits which have a date in column O of when they were 1st notified. I have another column U where I put the date of when we got to site. I have calculated the difference between the 2 dates in days in column V. I want to look to look up all the records in column O that are in a specific month & record any that have a result in column V of equal to or less than 3? Please help? I can share the worksheet if required? Many thanks!
7 Replies
See the attached sample workbook for a possible solution.
- Thank you v much, however I can enter the formula but it doesn't return any values? Can you confirm what the ROW & COLUMN part of the formula at the end should be pointing to? I have put the formula below to help you with what ive done so far??? Your help is much appreciated!
=IFERROR(INDEX('Call outs'!$O$3:$O$30, SMALL(IF(('Call outs'!$O$3:$O$30>=H11)*('Call outs'!$O$3:$O$30<=EOMONTH(H11,0))*('Call outs'!$V$3:$V$32<=3), ROW('Call outs'!$O$3:$O$32)-MIN(ROW('Call outs'!$O$3:$O$32))+1), ROW(AB1)), COLUMN(AB1)),"")- The first 'Call outs'!$O$3:$O$30 should be the entire range you want to extract from. Unless you want to return just a single column, it should not be column O alone.
- H11 should contain a date - the first day of the month you want to look at. In my sample workbook, I formatted the cell as mmm-yyyy. And since you're going to fill the formula to other cells, you should use $H$11 so that it remains the same when filled down or to the right.
- ROW(A1) and COLUMN(A1) should be used exactly like that in the first cell. They specify that the formula will extract the cell in the first row that matches the criteria, in the first column of the range. Excel will adjust A1 as needed when you fill or copy the formula to other cells.
- Finally, you must confirm the formula with Ctrl+Shift+Enter to turn it into an array formula.
