Forum Discussion

robwill100's avatar
robwill100
Brass Contributor
Dec 19, 2022

Hi, thanks for your help and actioning so quickly, greatly appreciated.

I was wondering if I could bother you with another query.
In the data table, there are some cells column H with "EE-NZ" and in column I with some items with "SF". Is it possible to exclude them when filtering the data in cell L3?

mtarler 

mtarler's avatar
mtarler
Silver Contributor
Dec 19, 2022

robwill100 I added those additional filter factors to the formula in L3:

=FILTER(FYFunded_Deals,IF($M$1="EE/SE",1,(FYFunded_Deals[Partner ID]=$M$1))*(FYFunded_Deals[Settlement Date]>=$O$1)*(FYFunded_Deals[Settlement Date]<=$P$1)*(FYFunded_Deals[Partner ID]<>"EE-NZ")*(FYFunded_Deals[Brokerage Invoice value]<>"SF"))

 

  • robwill100's avatar
    robwill100
    Brass Contributor
    Jan 31, 2023

    mtarler 

    Hi, you have previously assisted me with creating some formulas which is greatly appreciated. 

    I have since made some additions to the spreadsheet and have come against an issue when I am trying to calculate a particular result.

    In the attached spreadsheet you will see in cells W9 to Y14 that I have changed the formula to include a broader range of "Funded Deal" sizes. I have also changed the formulas in cells X10 to Y14 so that it calculates a result when "EE/SE" is chosen in cell M1. 

    What I haven't been able to do is create a formula that calculates the brokerage for each Funded deal size range when "EE/SE" is chosen. The previous LET formula you created for either EE or SE works, but it doesn't when I choose EE/SE.

    I would any guidance on how this might be achieved. 

     

    Cheers

     

    • mtarler's avatar
      mtarler
      Silver Contributor
      Jan 31, 2023

      robwill100  check the attached and see if this is what you want / need.

      I added the new higher range to W7 and also added W8 as the MAX in col Q and then I could make all the calculations use array formulas. 

      Lastly I got rid of the pid part of that equation since that whole range is already filtered based on the partner ID matching M1 so you don't need that as part of the countif again.

      • robwill100's avatar
        robwill100
        Brass Contributor
        Jun 18, 2023

        mtarler 

         

        Hi @mtarler, you previously assisted me with a spreadsheet and I am hoping I can ask for your assistance again.
        It is with the same spreadsheet and in essence, the same query as before as I have now added some extra items in the partner ID section.
        I originally had the top three and have now added EE-NZ, EE-UK & SF. I would like to be able to filter based on each item however the "select all" only needs to include EE/SE. I would appreciate your help once again.

        Cheers
        EE
        SE
        Select All
        EE-NZ
        EE-UK
        SF

         

Resources