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

[fran-d-31250]

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. 

[#2]

@fran-d-31250 

The SUMIFS function lets you specify more than one condition.

[#3]

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.

[#4]

@fran-d-31250 

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.

[#5]

@fran-d-31250 

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.

[#6]

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.

[#7]

@fran-d-31250 

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