help with SUMIFS and offset

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.

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.

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.

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.

robwill100 That was just the name of the table