我试图写一个If或SumIf来分别计算x,y和z的总和. 我可以使用一个简单的求和公式,但是这些都是成千上万的列,并且x,y和z是随机填充的.我尝试通过对colA进行排序来使用范围,但这是一个临时解决方案,而不是我想要的.
I'm trying to write an If or SumIf to calculate(sum) totals for x , y and z individually. I could use a simple sum formula but these are thousands of columns and x and y and z are populated randomly. I tried using a range by sorting colA but its a temporary solution and not what I am looking for.
我需要类似的东西:
If COL A has 'X' then add values corresponding to X in COL B示例:
COLA COLB ....... colx x 1 x 2 x 1 y 3 x 3 z 3 x 4我尝试查找其他类似问题的答案,但找不到适合我的正确答案.
I tried looking up other answers for similar questions but could not find the right one that works for me.
推荐答案如果要基于单个列中的值对多个列求和,则可以使用SUMPRODUCT这样
If you want to sum multiple columns based on a value in a single column you could use SUMPRODUCT like this
=SUMPRODUCT((A2:A100="x")*B2:X100)
总和范围内不能有文本,B2:X100,否则会出现错误-如果要允许该范围内的文本,请使用以下版本:
There can't be text in the sum range, B2:X100, otherwise you get an error - if you want to allow text in that range use this version:
=SUMPRODUCT((A2:A100="x")*ISNUMBER(B2:X100),B2:X100)
更多推荐
excel公式,可基于任何列中的另一个值(txt)对列中的值求和
发布评论