I want to calculate the sum of some cells from a row based on the value of the cell that is in front of it. See the image bellow for more details:
我想根据前面单元格的值计算一行中某些单元格的总和。有关详细信息,请参见下图:
Sumif nonadjacent cells in a row:
Sumif不连续的单元格连续:
Note that this is a simplistic version of my actual case. In my case I have hundreds of rows and tens of columns. It wouldn't be feasible to transpose the row into 2 columns, and I couldn't use nested ifs since you can use only 7. Is there any solution to this case? Any other recommendation would be most appreciated.
请注意,这是我实际案例的简单版本。在我的情况下,我有数百行和数十列。将行转换为2列是不可行的,我不能使用嵌套ifs,因为你只能使用7.这种情况有什么解决方案吗?任何其他建议将是最受欢迎的。
Thank you
1 个解决方案
#1
3
You can use SUMPRODUCT in this method:
您可以在此方法中使用SUMPRODUCT:
=SUMPRODUCT(--(H5:V15="A"),(I5:W15))
Notice that the two ranges are offset by one column.
请注意,这两个范围偏移一列。
Or you can also use SUMIF with offset columns:
或者您也可以将SUMIF与偏移列一起使用:
=SUMIF(H5:V15,"A",I5:W15)
The only rule between the two formulas is that the ranges be the same size.
两个公式之间的唯一规则是范围大小相同。
#1
3
You can use SUMPRODUCT in this method:
您可以在此方法中使用SUMPRODUCT:
=SUMPRODUCT(--(H5:V15="A"),(I5:W15))
Notice that the two ranges are offset by one column.
请注意,这两个范围偏移一列。
Or you can also use SUMIF with offset columns:
或者您也可以将SUMIF与偏移列一起使用:
=SUMIF(H5:V15,"A",I5:W15)
The only rule between the two formulas is that the ranges be the same size.
两个公式之间的唯一规则是范围大小相同。