Apr 05 2023 04:51 AM - edited Apr 05 2023 05:37 AM
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.
Apr 05 2023 04:56 AM
The SUMIFS function lets you specify more than one condition.
Apr 05 2023 05:36 AM
Apr 05 2023 06:06 AM
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.
Apr 05 2023 06:13 AM
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.
Apr 05 2023 06:15 AM
Apr 05 2023 08:26 AM
=SUMIFS(sales_column, month_column, specific_month, product_column, IF(specific_product="All Products", "*", specific_product))