Need help with formula for combining SUMIFS with IF from a dropdown list

Copper Contributor

I am building a sales dashboard that summarises sales by month and by product. I have SUMIFS functions that sum all product sales based on month which works fine. I am now trying to incorporate a dropdown so a user can select either Product A, Product B or All Products. If they select Product A, I want the formula to only sum Product A, and so on. I can only use the IF function for 2 arguments, it won't let me add the third element. 

6 Replies


The SUMIFS function lets you specify more than one condition.

Hi, sorry my mistake - I am using SUMIFS already to sum the sales based on month. I then need to add in a formula that includes the dropdown box as an argument in the function. Using SUMIFS isn't working for this as there are too many arguments.


You should be able to use a formula like


=SUMIFS(sales_column, month_column, specific_month, product_column, specific_product)


where specific_product is the cell with the product drop-down.


Alternatively, if I may add, you can use a nested IF function to achieve this.

Here is an example formula (as far as I understand) that you can adapt to your needs:

=IF(Dropdown="All Products", SUMIFS(Sales,Month,"January"), IF(Dropdown="Product A", SUMIFS(Sales,Month,"January",Product,"A"), SUMIFS(Sales,Month,"January",Product,"B")))


This formula checks the value of the Dropdown cell and sums the sales for all products, Product A or Product B based on the selected value.

You can replace Sales, Month, January, and Product with the appropriate cell ranges and values for your data.

Thanks Hans - I can get that far with it, but the third option for All Products needs to be a sum of Product A and Product B. Therefore Product A needs to somehow be both Product A and All Products, and Product B needs to be Product B and All Products.



=SUMIFS(sales_column, month_column, specific_month, product_column, IF(specific_product="All Products", "*", specific_product))