SUMIFS函数,仅对第一个为真的条件求和,忽略其余条件。

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

SUMIFS Function, only summing the first true conditions and omitting the remaining conditions

问题

I'm are trying to calculate the number of visits to a website by country, but we do not want to include all the countries on the list, only specific ones.
我正在尝试按国家计算网站的访问次数,但我们不想包括列表中的所有国家,只想包括特定的国家。

The formula I'm using works, however, it only sums the first country on the list that matches the true condition and not the remaining countries on the list.
我正在使用的公式有效,但它只会对列表中与条件匹配的第一个国家求和,而不是列表中的其余国家。

We have tried the following:
我们已经尝试了以下方法:

Screenshot from Sheets

Using the following formula:
使用以下公式:

=SUMIFS(C6:C17,B6:B17,A2:A3,A6:A17,E3)

and it isn't summing the visits for Spain and Italy
但它没有对西班牙和意大利的访问次数求和。

英文:

I'm are trying to calculate the number of visits to a website by country, but we do not want to include all the countries on the list, only specific ones.
The formula I'm using works, however, it only sums the first country on the list that matches the true condition and not the remaining countries on the list.

We have tried the following:

Screenshot from Sheets

Using the following formula

=SUMIFS(C6:C17,B6:B17,A2:A3,A6:A17,E3)

and it isn't summing the visits for Spain and Italy

答案1

得分: 0

=sum(ifna(filter(C6:C17, xmatch(B6:B17, A2:A3), A6:A17=E3)))

英文:

You may try:

=sum(ifna(filter(C6:C17,xmatch(B6:B17,A2:A3),A6:A17=E3)))

答案2

得分: 0

=SUMIFS(C6:C17,B6:B17,A2,A6:A17,E3) + SUMIFS(C6:C17,B6:B17,A3,A6:A17,E3)

英文:

In SUMIFS when you have more than one optional condition (Spain OR Italy, in this case) you can sum two SUMIFS, one for each of the conditions. In this case, when in your formula you say E2:E3 it's only grabbing the first value:

=SUMIFS(C6:C17,B6:B17,A2,A6:A17,E3)  +  SUMIFS(C6:C17,B6:B17,A3,A6:A17,E3)

huangapple
  • 本文由 发表于 2023年3月9日 19:38:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/75684088.html
匿名

发表评论

匿名网友

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

确定