Forum Discussion

mtarler's avatar
mtarler
Silver Contributor
Dec 17, 2022

robwill100 see attached but 

a) to sumif the brokerage $$ based on the same TAF groups you just need to change the 1st term of the SUMIFS from TAF column to the Brokerage column

b) you don't need to do the offset trick just use the spill range you created L3# (the # means use the whole spill range ) and then INDEX just the column you want

  • 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

         

Resources