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!

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 16, 2021

        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.

Resources