在Excel中使用sumproduct计算值
我尝试过 sumproduct 并尝试过 sumif 但我认为我需要的是组合（或更好地理解 sumproduct）
这是我的数据
状态|百分比
新南威尔士州 | 0
新南威尔士州 | 20
维多利亚 | 0
SA | 0
西澳 | 15
新南威尔士州 | 0
新南威尔士州 | 70
我想尝试计算如下：
- 其中州=新南威尔士州
并且
- 其中百分比> 0
我想计算出匹配值的平均值
通过包含值 = 0 我可以使用：
=SUMIF(A:A,"NSW",B:B)/COUNTIF(A:A,"NSW")
但现在我想通过删除 0 值来进一步定义。
谢谢
I've tried sumproduct and i've tried sumif but I think what I need is a combination (or a better understanding of sumproduct)
Here is my data
state | percent
NSW | 0
NSW | 20
VIC | 0
SA | 0
WA | 15
NSW | 0
NSW | 70
What I want to try and calculate is as follows:
- Where state = NSW
AND
- Where percent > 0
I want to work out the average of the matched values
By including values = 0 I can use:
=SUMIF(A:A,"NSW",B:B)/COUNTIF(A:A,"NSW")
But now I want further define by removing the 0 values.
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以使用此
条件总计/条件计数方法
假设您的数据集位于 A1:B7，请根据需要进行更新
所有 Excel 版本
=SUMPRODUCT(--(A1:A7="新南威尔士州"),--(B1:B7>0),B1:B7)/SUMPRODUCT(--(A1:A7="新南威尔士州"),--( B1:B7>0))仅限 Excel 07/10
=SUMIFS(B1:B7,B1:B7,">0",A1:A7,"新南威尔士州")/COUNTIFS(B1:B7,">0",A1:A7,"新南威尔士州")< /代码>You could use this
criteria total/criteria count approach
assumes your dataset is in A1:B7, please update as necessary
All Excel versions
=SUMPRODUCT(--(A1:A7="NSW"),--(B1:B7>0),B1:B7)/SUMPRODUCT(--(A1:A7="NSW"),--(B1:B7>0))Excel 07/10 only
=SUMIFS(B1:B7,B1:B7,">0",A1:A7,"NSW")/COUNTIFS(B1:B7,">0",A1:A7,"NSW")在 Excel 2007 及更高版本中，您可以使用 AVERAGEIFS
=AVERAGEIFS(B1:B7,B1:B7,">0",A1:A7,"NSW")In Excel 2007 and later you can use AVERAGEIFS
=AVERAGEIFS(B1:B7,B1:B7,">0",A1:A7,"NSW")