我想要得到以下内容:
如果单元格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语句的最后一部分,我尝试了“<>”,“<>”和“”,“<>”和“”,我得到错误:公式包含无法识别的文本
1 回答
你可以这样做是一个任何
IF
条件的声明 .因为您的列偏移基本上由单元格
$B$3
定义,然后使用OFFSET
公式来计算列的来源: