Forum Discussion

Murray1985's avatar
Murray1985
Copper Contributor
May 16, 2021

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 w...
Formulas and Functions
  • Murray1985's avatar
    Murray1985
    Copper Contributor
    May 16, 2021
    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)),"")
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      May 16, 2021

      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.
      • Murray1985's avatar
        Murray1985
        Copper Contributor
        May 16, 2021

        HansVogelaar 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!