包含值的偏移单元格的总和 [英] SUM of offset cells that contain a value
问题描述
我正在尝试制作偏移单元格的基本SUM。基本上我有4列,列A只是一个标题,列B是一个计数的数字,列C和D引用另一个值。
我正在尝试的事情要完成的是列G上的数量。它应该是列B中所有值的总和。如果该数字出现在参考列中,那么这个数字就会出现在列B中。
一个例子是G2等于15,因为1出现在C2中(所以它应该从B2中增加7)和D3并添加8从B3)。
我有基本的 = COUNTIF
函数来计算实例,但怎么做如果一个值出现在列C或D中,我引用列B的值?
= COUNTIF(C2:D4,1)
= COUNTIF(C2:D4,2)
...
https://docs.google.com/spreadsheets/d/1h9PDZii5wO866Hky3P_BZ_SOwuHAAd6qUySkuJ14L6A/edit?usp=sharing
作为 Reference1
和 Reference2
在同一行中不会有相同的编号:
$ ($($ C $ 2:$ C $ 4 = F2)+($ D $ 2:$ D $ 4 = F2))* $ B $ 2:
pre>
= SUMPRODUCT $ 4)
如果它们可能具有相同的数字,但您只想计算值在 Count
列一次,然后:
= SUMPRODUCT(( (($ C $ 2:$ C $ 4 = F2)+($ D $ 2:$ D $ 4 = F2))> 0)* $ B $ 2:$ B $ 4)
/ pre>
I'm trying to make a basic SUM of offsetted cells. Basically I have 4 columns, column A being just a title, column B being a counted number, and columns C and D referencing another value.
The thing I'm trying to accomplish is the number count on column G. It should be a SUM of all of the values in column B IF that number appears in the reference columns
An example would be that G2 would equal 15, since 1 appears in C2 (so it should add 7 from B2) and D3 (and add 8 from B3).
I have the basic
=COUNTIF
function to count the instances, but how do I reference the value from column B if a value appears in column C or D?
=COUNTIF(C2:D4,"1")
=COUNTIF(C2:D4,"2")
...https://docs.google.com/spreadsheets/d/1h9PDZii5wO866Hky3P_BZ_SOwuHAAd6qUySkuJ14L6A/edit?usp=sharing
解决方案You can use
SUMPRODUCT
.So long as
Reference1
andReference2
will not have the same number in the same row:=SUMPRODUCT((($C$2:$C$4=F2)+($D$2:$D$4=F2))*$B$2:$B$4)
If it is possible for them to have the same number, but you only want to count the value in the
Count
column once, then:=SUMPRODUCT(((($C$2:$C$4=F2)+($D$2:$D$4=F2))>0)*$B$2:$B$4)
这篇关于包含值的偏移单元格的总和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!