i have a column A with formula values: 1 op, 2 op, 4 op, 9 op, 234 op and another with names corresponding to those values: mike1, mike3, mike8, mike2, mike1. How can i sum the values for each name? i have tried with sumif, count but it just doesn't sum them. I have the formula to show the unique names in alphabetic order but i can't make the sum. Thank you.
我有一个公式值为A的列:1 op,2 op,4 op,9 op,234 op和另一个名称对应于这些值:mike1,mike3,mike8,mike2,mike1。如何将每个名称的值相加?我已经尝试过sumif,count但它只是不加总和它们。我有一个公式来按字母顺序显示唯一的名称,但我不能总和。谢谢。
------------------------------------------
| A | B | C | D | E |
------------------------------------------
|1 OP | mike1 | | mike1 | 235 op |
------------------------------------------
|2 OP | mike3 | | mike3 | 11 op |
------------------------------------------
|4 OP | mike8 | | mike8 | 4 op |
------------------------------------------
|9 OP | mike3 | | | |
------------------------------------------
|234 OP | mike1 | | | |
------------------------------------------
1 个解决方案
#1
3
As @Dirk said - column A needs to be numerical. "1" is numerical, "OP" isn't.
正如@Dirk所说 - A栏需要数字化。 “1”是数字,“OP”不是。
Based on the example table you gave this formula would work if column A was numerical: =SUMIF($B$1:$B$5,$D1,$A$1:$A$5)
基于示例表,如果列A为数字,则表示此公式将起作用:= SUMIF($ B $ 1:$ B $ 5,$ D1,$ A $ 1:$ A $ 5)
If you wanted column A to be numerical you need to remove the OP text from the cell. You could display the OP by using a custom number format of: # "OP" - the SUMIF formula will work then.
如果您希望列A为数字,则需要从单元格中删除OP文本。您可以使用以下自定义数字格式显示OP:#“OP” - 然后SUMIF公式将起作用。
Or you could use this formula to remove the OP from the cell and calculate the number: =SUMPRODUCT(($B$1:$B$5=$D1)*(SUBSTITUTE($A$1:$A$5," OP",""))) & " OP"
或者您可以使用此公式从单元格中删除OP并计算数字:= SUMPRODUCT(($ B $ 1:$ B $ 5 = $ D1)*(SUBSTITUTE($ A $ 1:$ A $ 5,“OP”,“ “)))和”OP“
#1
3
As @Dirk said - column A needs to be numerical. "1" is numerical, "OP" isn't.
正如@Dirk所说 - A栏需要数字化。 “1”是数字,“OP”不是。
Based on the example table you gave this formula would work if column A was numerical: =SUMIF($B$1:$B$5,$D1,$A$1:$A$5)
基于示例表,如果列A为数字,则表示此公式将起作用:= SUMIF($ B $ 1:$ B $ 5,$ D1,$ A $ 1:$ A $ 5)
If you wanted column A to be numerical you need to remove the OP text from the cell. You could display the OP by using a custom number format of: # "OP" - the SUMIF formula will work then.
如果您希望列A为数字,则需要从单元格中删除OP文本。您可以使用以下自定义数字格式显示OP:#“OP” - 然后SUMIF公式将起作用。
Or you could use this formula to remove the OP from the cell and calculate the number: =SUMPRODUCT(($B$1:$B$5=$D1)*(SUBSTITUTE($A$1:$A$5," OP",""))) & " OP"
或者您可以使用此公式从单元格中删除OP并计算数字:= SUMPRODUCT(($ B $ 1:$ B $ 5 = $ D1)*(SUBSTITUTE($ A $ 1:$ A $ 5,“OP”,“ “)))和”OP“