Forum Discussion
Need help with formula for combining SUMIFS with IF from a dropdown list
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
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.
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.
