Hi all, I have a couple of formulas I need help with. In the attached spreadsheet:In column A-J I have a data set for which I am trying to calculate some financial outcomes.I have created a filter function in cell L3 which shows the data based on the selection in M1.In column X I have created a SUMIFS formula to extract the SUM of deals that fall between the ranges in W3-W6 based on the values in column Q (TAF) What I need help with is to SUM the corresponding values in column T (Brokerage Invoice Value) so I know how much brokerage was generated based on the size of the values in column T. The other formula I need help with is how to automatically extend the data range when I add new inputs to the data table.I have used the OFFSET function in cell S1 but don’t know how to apply it to the calculations in cells X3 to X6. Any guidance would be greatly appreciated.

Thanks for getting back to me so promptly, that's worked a treat.

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 columnb) 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

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

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"))

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

help with SUMIFS and offset | Microsoft Community Hub

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
Example filter extract.xlsx214 KB
- 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
  Example filter extract (1).xlsx214 KB
  - 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"))
    
    Example filter extract (1).xlsx215 KB

Forum Discussion

help with SUMIFS and offset

Share

Resources