SUMIFS嵌套在IF语句中-不包括空格

Question

我正在尝试获取以下内容：

如果单元格b3等于P1，则只要列D等于单元格d6且列E不为空，则求和列R，如果单元格b3不等于P1，则.....

下面是公式：

=IF($B$3="P1",SUMIFS('Budget Money'!R:R,'Budget Money'!D:D,D6,'Budget Money'!E:E,"<>"),
 IF($B$3="P2",SUMIFS(‘Budget Money’!R:R,'Budget Money'!D:D,D6,'Budget Money'!F:F,"<>"),
 IF($B$3="P3",SUMIFS(‘Budget Money’!R:R,'Budget Money'!D:D,D6,'Budget Money'!G:G,"<>"),
 IF($B$3="P4",SUMIFS(‘Budget Money’!R:R,'Budget Money'!D:D,D6,'Budget Money'!H:H,"<>"),
 IF($B$3="P5",SUMIFS(‘Budget Money’!R:R,'Budget Money'!D:D,D6,'Budget Money'!I:I,"<>"),
 IF($B$3="P6",SUMIFS(‘Budget Money’!R:R,'Budget Money'!D:D,D6,'Budget Money'!J:J,"<>"),
 IF($B$3="P7",SUMIFS(‘Budget Money’!R:R,'Budget Money'!D:D,D6,'Budget Money'!K:K,"<>"),
 IF($B$3="P8",SUMIFS(‘Budget Money’!R:R,'Budget Money'!D:D,D6,'Budget Money'!L:L,"<>"),
 IF($B$3="P9",SUMIFS(‘Budget Money’!R:R,'Budget Money'!D:D,D6,'Budget Money'!M:M,"<>"),
 IF($B$3="P10",SUMIFS(‘Budget Money’!R:R,'Budget Money'!D:D,D6,'Budget Money'!N:N,"<>"),
 IF($B$3="P11",SUMIFS(‘Budget Money’!R:R,'Budget Money'!D:D,D6,'Budget Money'!O:O,"<>"),
 IF($B$3="P12",SUMIFS(‘Budget Money’!R:R,'Budget Money'!D:D,D6,'Budget Money'!P:P,"<>")))))))))))))

对于sumifs语句的最后部分，我尝试了"<>"，"<>"&""，"<>"&"“，我得到了错误:公式包含无法识别的文本

Answer 1

您可以使用任意IF条件的一条语句来完成此操作。

因为您的列偏移量基本上是由单元格$B$3定义的，所以将其与OFFSET公式一起使用来计算列的来源：

=SUMIFS('Budget Money'!R:R,'Budget Money'!D:D,D7,OFFSET('Budget Money'!D:D,0,RIGHT(B1,LEN(B1)-1)),"<>")