如下面的屏幕截圖所示，您的表格包含一個“類別”列和一個“金額”列，並且您已創建了包含所有類別的數據驗證下拉列表。 從下拉列表中選擇任何類別時，您希望總結B列中的所有相應單元格值，並將結果填充到指定的單元格中。 例如，從下拉列表中選擇類別CC時，需要對單元格B5和B8中的值進行求和，並獲取總數40 + 70 = 110。 如何實現它？ 本文中的方法可以幫助您。
1。 選擇一個空白單元格填充求和結果，然後輸入公式 =SUMIF(A2:A8,D2,B2:B8) 進入配方欄，然後按下 輸入 關鍵是要得到結果。 看截圖：
1。 你也可以使用這個公式： =SUMPRODUCT(SUMIF($A$2:$A$8,$D$2,$B$2:$B$8)) 根據下拉列表的選擇來求和值。
2。 在公式中，A2：A8是類別範圍，D2是下拉列表單元格，B2：B8是您需要基於下拉列表選擇求和的值範圍。 請根據需要更改它們。
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 months agoThe Sum Values Based On Selection Of Drop-Down List With Formula worked perfectly for what I needed. Thanks so much for posting this on here. You saved me hours of headaches!
To post as a guest, your comment is unpublished.· 1 years agoI am trying to use sumifs with set of conditions. There is a project column with sprint numbers and test cases, etc.. I created a drop down list of the project names above the cell with the formula and referred that in the Sumif condition. Now based on my dropdown selection, the total of test cases created are resulted. This works absolutely fine. Now, if I have to select the dropdown to select all projects, how do I do it. I tried creating a list item as "<>" but that is not working. Any suggestions?
To post as a guest, your comment is unpublished.· 4 months agoTrying using the wild card "*" so the last or first item in your list is just the *. I am working through a similar problem and that has worked most of the time.
To post as a guest, your comment is unpublished.· 1 years agoDid you find a way to make this work?
To post as a guest, your comment is unpublished.· 1 years agoHi,
What do you mean of "to select the dropdown to select all projects"?
Would be nice if you could provide screenshot of what you are trying to do.
Thanks for your comment.
To post as a guest, your comment is unpublished.· 1 years agoformula for 2 different categories
To post as a guest, your comment is unpublished.· 1 years agohow to sum value all selected cell in sum total value ?