Excel-SUMIFS用于多列 [英] Excel - SUMIFS for multiple columns

问题描述

如果同一行中的其他单元格符合预定义的条件，则我需要对几列的值求和.仅三列的工作公式如下:

I need to sum the values of several columns, if other cells in the same row match a predefined criteria. The working formula for only 3 columns is the following:

=SUM(SUMIFS(‘Sheet1'!W:W; ‘Sheet1'!$B:$B;"Sales";‘Sheet1'!$C:$C;">=4");SUMIFS(‘Sheet1'!X:X; ‘Sheet1'!$B:$B;"Sales";‘Sheet1'!$C:$C;">=4");SUMIFS(‘Sheet1'!Y:Y; ‘Sheet1'!$B:$B;"Sales";‘Sheet1'!$C:$C;">=4"))

我将需要对多个单元格使用该公式(每次总计10列以上)，并且需要手动更改列，因此我需要通过以下方式使用相同的公式:

I will need to use the formula for several cells (and sum more than 10 columns per time) and I will need to change the columns manually, so I need the same formula in the following way:

=SUMIFS(‘Sheet1'!W:Y; ‘Sheet1'!$B:$B;"Sales";‘Sheet1'!$C:$C;">=4")

，但是当前此公式导致"#VALUE！"错误.原因是(我假设)使用了多个列" W:Y "您能建议一种解决方法吗?

,but currently this formula leads to a "#VALUE!" error. The reason for that is (I assume) the use of multiple columns "W:Y" Can you suggest a workaround?

推荐答案

我建议使用SUMPRODUCT而不是SUMIFS.您可以构建类似的东西:

I would suggest to use SUMPRODUCT rather than SUMIFS. You can build something like that :

=SUMPRODUCT((B1:B1048575="Sales")*(C1:C1048575>=4)*(W1:Y1048575))

SUMPRODUCT的缺点是您不能使用整个列(例如，您不能编写SUMPRODUCT((B:B ="Sales" ...))，这会产生错误).

The downside of SUMPRODUCT is that you can't use a whole column (for example you cannot write SUMPRODUCT((B:B="Sales"...)), this would generate an error).

希望这会有所帮助.

这篇关于Excel-SUMIFS用于多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！