Forum Discussion

kabeerkhann's avatar
kabeerkhann
Copper Contributor
Sep 13, 2023

2013 doesn't have =filter() function. ANY alternate function?

I have Microsoft excel 365 in that filter function is available and i create a follow up sheet linked with database sheet where in database user will store data from the form. In database there is a column Follow up date.

I want to show the all the data which will meet a date condition. I have done by using filter function but in 2013 it doesn't have filter function what function can i use to perform the same operation? 

  • ecovonrein's avatar
    ecovonrein
    Iron Contributor

    There is a way to abuse SUMPRODUCT to produce a SQL statement. There is a way to arrange an Excel sheet like an SQL table. My database has plants (across rows by name, each plant occupying multiple rows labelled by attributes like "Production") and associated data (across columns by Dates). When all data be called "DBase" and the column headers "Dates", then your can filter (query) the production data as follows:
    1 =SUMPRODUCT((Plants="Apollo")*(Attribute="Production")*ROW(Plants))
    to obtain the row number of the total production data for Apollo and
    2 =SUMPRODUCT((31-12-2021<Dates)*(Dates<=31-12-2022)*INDEX(Dbase,rowAbove,0))
    Hope this makes sense.

  • kabeerkhann 

    =IFERROR(INDEX($A$2:$A$23,SMALL(IF($B$2:$B$23>60,ROW($B$2:$B$23)-1),ROW(A1))),"")

    An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula filters the values in column A if the corresponding value in column B is greater than 60. The screenshot is taken from Excel 2013.

     

     

     

Resources