SUMIFS Using Multiple Combinations of Critera

Matts2212

Hi,

Apologies for the (probably) terrible and non-specific title- I'm really struggling to summarise what I want to do concisely!

I'm trying to create a 'summary sheet' which performs a calculation on some data using criteria which the users selects from drop downs, see screenshot below:

Screenshot 2021-07-07 at 11.51.08.png

Assuming the user must select something in each drop down, I found this easy- the table in columns X to AG just calculates using a SUMIFS (e.g. sum if the data in column ABC in the data sheet = C6, and if column ABD = C7 etc for each of the 5 criteria).

However what I'd like this to do ideally is allow the user to select any combination of specifications- i.e. they don't have to select a drop down for all 5. E.g. perhaps they want to calculate the response rates for all UK campaigns, or all UK skincare campaigns, not just UK+Skincare+Sachet+Youtube+Survey etc.

Is this possible in excel, and if so, how?

I've probably explained this terribly badly so let me know if you need further info- and I really appreciate any help in advance (even if its just to say sorry, no can do)

Bassam Khalil

Hi @Matts2212

Hope you are fine, you can do that by using SUM With Collect Functions. if you like i can create those formula for you but i need you to share me with admin permission on a sample copy of a workspace contains a sample of your sheets (after removing or replacing any sensitive information).

My Email: Bassam.k@mobilproject.it

Bassam Khalil

Hi @Matts2212

Hope you are fine, you can do that by using SUM With Collect Functions. if you like i can create those formula for you but i need you to share me with admin permission on a sample copy of a workspace contains a sample of your sheets (after removing or replacing any sensitive information).

My Email: Bassam.k@mobilproject.it

Matts2212

@Bassam Khalil

Thank you for the kind offer however I've realised I'm massively over complicating it- I just need to use a pivot table!

Doesn't quite look as nice but same outcome.