在Excel中按多个条件求列的总和

huangapple go评论89阅读模式
英文:

Sum columns by multiple criteria in excel

问题

我在尝试在Excel中添加列时遇到了麻烦。

我只想对那些白天数为零的日子的夜间数字进行求和。

你可以看到下面的长公式:

=IF(A3=0;B3;0)+IF(C3=0;D3;0)+IF(E3=0;F3;0)+IF(G3=0;H3;0)

结果应该是15。因为星期二和星期三的白天没有数字,所以应该对夜间数字进行求和。

这对于4天来说是简短的,但对于一个月来说写这个公式会很长。

提前谢谢你。

英文:

I'm having trouble finding a short formula when adding columns in excel.

A B C D E F G H
1 Monday Monday Tuesday Tuesday Wednesday Wednesday Thursday Thursday
2 Day Night Day Night Day Night Day Night
3 10 5 0 10 0 5 10 10

I want to sum only the night of the days whose day number is zero.
You can see the long formula below.

=IF(A3=0;B3;0)+IF(C3=0;D3;0)+IF(E3=0;F3;0)+IF(G3=0;H3;0)

The result should be 15. Since there are no numbers during the daytime on Tuesdays and Wednesdays, it should be summing night numbers.
It is short for 4 days but very long to write this formula for a month.
Thank you in advance.

答案1

得分: 1

尝试使用 SUMPRODUCT()

=SUMPRODUCT((A4:I4=0)*(A3:I3="Day")*(B4:J4))

或者 SUMPRODUCT()

=SUM(FILTER(B4:J4,(A3:I3="Day")*(A4:I4=0)))

英文:

Try SUMPRODUCT().

=SUMPRODUCT((A4:I4=0)*(A3:I3="Day")*(B4:J4))

Or SUMPRODUCT().

=SUM(FILTER(B4:J4,(A3:I3="Day")*(A4:I4=0)))

在Excel中按多个条件求列的总和

huangapple
  • 本文由 发表于 2023年8月10日 16:05:43
  • 转载请务必保留本文链接:https://go.coder-hub.com/76873756.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定