SUMIFS Using Multiple Combinations of Critera

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:

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)

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 07/07/21 Answer ✓

    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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 07/07/21 Answer ✓

    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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!