Look up values equal or less than 3 based in a specific month

[Murray1985]

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!

[#2]

@Murray1985 

See the attached sample workbook for a possible solution.

[#3]

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)),"")

[#4]

@Murray1985 

1. 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.
2. 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.
3. 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.
4. Finally, you must confirm the formula with Ctrl+Shift+Enter to turn it into an array formula.

[#5]

@Hans Vogelaar Thanks... ive got it to work but it isnt quite what i am looking for.... I think its a countifs formula I need. For example cell K13 on the "KPI's" sheet to return the number of records in column V on the "Call outs" sheet where the criteria in column O on the "Call outs" sheet is the same month as cell J11 on the "KPI's" sheet and the value in column V on the "Call outs" sheet is equal or less than 3.

I have attached a sample sheet to help it make more sense!!! Appreciate your help!

[#6]

@Murray1985 

My apologies, you did mention that you wanted to count records, but then I misread the remainder of your post.

In B13:

=COUNTIFS('Call outs'!$O$3:$O$65,">="&B11,'Call outs'!$O$3:$O$65,"<="&EOMONTH(B11,0),'Call outs'!$V$3:$V$65,"<=3")

This is an 'ordinary' formula, you don't have to confirm it with Ctrl+Shift+Enter.

Copy to D13, F13 etc.

[#7]

@Hans Vogelaar Thanks this seems to work however I have attached the spreadsheet again as for some reason even though there is no data in column v on the "Call outs" sheet, a value is returned in cells C13, E13, G13, I13, K13, W13 & Y13 on the "KPI's" sheet??? Could you help me please?

Thanks

[#8]

@Murray1985 

There are values in column V but they have been hidden somehow.

Select V3:V4336 and select Clear > Clear Formats on the Home tab of the ribbon.