Forum Discussion

ajl_ahmed's avatar
ajl_ahmed
Iron Contributor
Dec 24, 2022
Solved

Filtering Data

Hi I need to use the FILTER function to filter the range (C6:Q55) of the attached file to get the student name who got degree less the 50 in any defined subject (D4:Q4). The defined subjects should ...
excel
office 365
  • Lorenzo's avatar
    Lorenzo
    Dec 24, 2022

    Hi ajl_ahmed

    Does this function update dynamically? I mean that if I change the degree from <50 to >50 the elements will be changed? => Why don't you try???

     

    A sligthly more concise one:

    =FILTER(C6:C55,
  MMULT(--(D6:Q55 < 50),SEQUENCE(COLUMNS(D4:Q5)))
)

     

MY's avatar
MY
Copper Contributor
Dec 24, 2022
Here you go, try this:
=FILTER(C6:C55,MMULT(IF(D6:Q55<50,1,0),SEQUENCE(COLUMNS(D4:Q5),1,1,0)))
  • RainSadu's avatar
    RainSadu
    Copper Contributor
    Dec 24, 2022
    Good - super
  • ajl_ahmed's avatar
    ajl_ahmed
    Iron Contributor
    Dec 24, 2022
    Thx. for your reply
    Does this function update dynamically? I mean that if I change the degree from <50 to >50 the elements will be changed?
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Dec 24, 2022

      Hi ajl_ahmed

      Does this function update dynamically? I mean that if I change the degree from <50 to >50 the elements will be changed? => Why don't you try???

       

      A sligthly more concise one:

      =FILTER(C6:C55,
  MMULT(--(D6:Q55 < 50),SEQUENCE(COLUMNS(D4:Q5)))
)

       

Resources