May 16 2021 10:44 AM
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!
May 16 2021 11:18 AM
See the attached sample workbook for a possible solution.
May 16 2021 11:45 AM
May 16 2021 12:45 PM
May 16 2021 01:18 PM
@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!
May 16 2021 01:35 PM
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.
May 16 2021 02:49 PM
@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
May 17 2021 01:26 AM
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.