help with SUMIFS and offset

[robwill100]

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.

[#2]

@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

[#3]

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 

[#4]

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

[#5]

@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

[#6]

@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.

[#7]

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

[#8]

@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

[#9]

@robwill100  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.

[#10]

@mtarler 

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

[#11]

@robwill100 That was just the name of the table

[#12]

@mtarler Thanks, I realised that just after I sent the email.

If ok with you I have another query.

I would appreciate some guidance as to how to build the report per below.

In the attached spreadsheet I have a set of data that I want to be able to filter based on multiple criteria and the results to then be extracted to a new sheet within the same workbook.

I have created a version where I was using connected dropdown boxes however, I ran into some trouble when I wanted to select multiple criteria within a single column. Additionally if I wanted to reset the data so I could see all results I was unable to create a function to do that.

In the spreadsheet I have the below column names. Within each column there are multiple items. Eg, for supplier there is supplier 1, supplier 2, supplier 3 etc, in “State” there is NSW, ACT, VIC etc.

As an example, I want to be able to create a report where I can choose a combination of data, say. For  “state” = choose VIC, For supplier choose “Test ABC”, For lender Name choose “Finance 2” & for equipment type chose “Telco”.

I would then like for the output of the report to show and then if required extract the data to another sheet.

I would also like to be able to multiple criteria from each column. For example when I chose the equipment type, I want to be able to choose “telco & solar”.

I would love some guidance on the best way to build such a report.

Pmt Plan Type

ApplicantName

State

Supplier Name

CP Brand

Amount

FundedDate

Sched term end date

Lender Name

Equipment Type

ApplicantEmail

[#13]

@robwill100  I thought you could use PivotTable and the built in filters and splicers and such and maybe you can but I put together a filtering formula in the attached that is pretty robust/flexible.  I created 2 tabs: dropdown to dynamically pull all unique options from the table and output where the output will be generated.  on the ouput tab there is a row to enter filter terms (or use drop downs).  If you want to filter for multiple items just manually list them separated by a comma (unfortunately multi-select does NOT work on the drop downs).  I also give an option to have the filters expect full cell value or partial values.  So with that setting in H1 set true you must type 'Company 123' to match 'COMPANY 123' (case doesn't matter) but with it false then '123' would match 'COMPANY 123' but would also match 'COMPANY 123456'.  Hope this is what you wanted.

[#14]

@mtarler 

Hi, you have kindly assisted me before with excel queries and I am hoping you can assist me again.

In the attached spreadsheet is a set of data I extract from our CRM system to show the activity of one of our users. We use the data for monthly goal setting and KPI's for performance.

I want to be able to easily extract the data into a chart without having to go through the steps of deleting blank rows and reformatting the data.
I am not sure what is the best approach, ie do I create a pivot table first or is there a way to just choose the data that is highlighted without having to do the re-formatting.

I would appreciate your thoughts.

[#15]

@robwill100 

It depends on which chart you'd like to have. In your sample not one, two users. Will it be one chart for all users in the report, or for each user separate chart. If the latest you may start from Analyze Data -select highlighted block, click on icon, select most suitable chart and do cosmetic.

If combined chart for all that's more job for Power Query - query you activity report file, or better CRM directly, make transformation, load data to data model and build chart from it.

But again, it all depends on which result is expected.