Forum Discussion
help with SUMIFS and offset
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
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.
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
SFrobwill100 OK so for the drop down you just need to add 'categories' to column AD. That said I made the filter 'smarter', which I hope does what you want. So the filter now will list everyone if you select 'Select All', and if you pick any value that exists in the list it will filter for only that value, but if it is not in the list (i.e. "EE/SE") it will split based on "/" and select based on the individual terms separated by "/". So you could add "EE-NZ/EE-UK" to get that combo should you choose. I even made it so you could have more than 2 so "EE/SE/SF" would pull all 3. I added that as an example but feel free to delete it.
Hi, thanks for getting back to me so promptly.
The file you updated was an extract of the actual data file which has additional information in it.
I have applied the updates to the dropdown box and updated the sheet references based on the actual file.
Where I am running into trouble is where you have added a named range
=FILTER(FYFunded_Deals,IF($B$2="Select All",1,IF(ISNUMBER(XMATCH($B$2,Settlements_FY21_23[Partner ID])),Settlements_FY21_23[Partner ID]=$B$2,LET(L,TEXTSPLIT($B$2,"/"),MMULT(--(Settlements_FY21_23[Partner ID]=L),EXPAND({1},COLUMNS(L),1,1)))))*(Settlements_FY21_23[Settlement Date]>=$D$2)*(Settlements_FY21_23[Settlement Date]<=$E$2))
Would you mind applying the formula to the attached sheet as it is not currently calculating, I assumed due to the incorrect reference in the named range.
Cheers
- Thanks for getting back to me so promptly, that's worked a treat.