Forum Discussion

Hussein_Mohamed's avatar
Hussein_Mohamed
Brass Contributor
Oct 25, 2023
Solved

formula assist

Dear Gents, i have a formula and need to add a condition if i need to get data if meet the condition for both parts of formula like(if range()="Contract",get the data matched   VSTACK(FILTER('Unde...
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Oct 25, 2023

    Hussein_Mohamed 

    That could be

    =FILTER( 'Under Collection'!C:C,
    ISNUMBER(SEARCH(Aging!$B$2,'Under Collection'!C:C))*
    ISNUMBER(SEARCH($C$2,'Under Collection'!B:B))
)

    - AND(), OR() don't work with the FILTER(). Use * and + instead

    - FIND() is case sensitive, I guess in your case SEARCH() is more suitable

mtarler's avatar
mtarler
Silver Contributor
Oct 25, 2023
To add additional conditions in a FILTER just multiply them (conditional 1)*(conditional 2) so
VSTACK(FILTER('Under Collection'!B:B,ISNUMBER(FIND(Aging!$B$2,'Under Collection'!C:C))*(range2="Contract")),FILTER(AR!B:B,ISNUMBER(FIND(Aging!$B$2,AR!C:C))*(range2="Contract")))
That said I recommend you convert the data on 'Under Collection' and 'AR' into Tables and then you can reference that table data instead of the entire column of the sheet. you can also combine them and then FILTER instead:
FILTER(VSTACK(Under_Collection_Table[ColBname],AR_Table[ColBname]),ISNUMBER(FIND(Aging!$B$2,VSTACK(Under_Collection_Table[ColCname], AR_Table[ColCname])))*(range2="Contract"))
Hussein_Mohamed's avatar
Hussein_Mohamed
Brass Contributor
Oct 25, 2023

mtarler 

thank you for your reply, i am unfortuntly fail to add the criteria in the formula, i have attached the original sheet culumn B is the range in both sheets.

if you please reform the formula in the aging sheet to get the data in a simple form.

 

Thanks

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 25, 2023

    Hussein_Mohamed 

    That could be

    =FILTER( 'Under Collection'!C:C,
    ISNUMBER(SEARCH(Aging!$B$2,'Under Collection'!C:C))*
    ISNUMBER(SEARCH($C$2,'Under Collection'!B:B))
)

    - AND(), OR() don't work with the FILTER(). Use * and + instead

    - FIND() is case sensitive, I guess in your case SEARCH() is more suitable

    • Hussein_Mohamed's avatar
      Hussein_Mohamed
      Brass Contributor
      Oct 25, 2023
      Hartfull thank you for your support, it works well