替换 Excel 2003 中的 SUMIFS
所以，我需要找到一个 Excel 2003 替代 =SUMIFS，这只是 2007+（显然）。
该公式用于从收入列表生成此汇总数据表，其中每个收入行都有字段类型（静态、电子邮件或外展）和字段基金（ABC、QRS 和 XYZ）。
type fund total count average
static ABC $12,390.88 171 $72.46
email ABC $6,051.32 65 $93.10
outreach ABC $8,835.00 138 $64.02
static QRS $12,925.44 79 $163.61
email QRS $9,305.44 99 $93.99
outreach QRS $1,799.00 49 $36.71
static XYZ $4,912.20 36 $136.45
email XYZ $75.00 2 $37.50
outreach XYZ $0.00 0 #DIV/0!
这是公式，
`=SUMIFS('revenue'!G:G,'revenue'!AH:AH,Sheet2!A2,'revenue'!AI:AI,Sheet2!B2)`
其中G是美元金额，AH和AI匹配类型或基金栏。
我如何让它在 Excel 2003 中工作？
So, I need to find an Excel 2003 substitute for =SUMIFS, which is only 2007+ (apparently).
The formula is used to generate this summary data table, from a list of revenue, where each revenue line has the field type (static, email or outreach) and the field fund (ABC, QRS and XYZ).
type fund total count average
static ABC $12,390.88 171 $72.46
email ABC $6,051.32 65 $93.10
outreach ABC $8,835.00 138 $64.02
static QRS $12,925.44 79 $163.61
email QRS $9,305.44 99 $93.99
outreach QRS $1,799.00 49 $36.71
static XYZ $4,912.20 36 $136.45
email XYZ $75.00 2 $37.50
outreach XYZ $0.00 0 #DIV/0!
This is the formula
`=SUMIFS('revenue'!G:G,'revenue'!AH:AH,Sheet2!A2,'revenue'!AI:AI,Sheet2!B2)`
Where G is a dollar amount, and AH and AI are matching the type or fund column.
How do i get this to work in Excel 2003?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我最终解决这个问题的方法是创建一个列，该列组合了我需要检查的两列（ABCstatic、ABCemail 等）。这样做允许我只使用一个“SUMIF”（使用两个链接在一起的 SUMIF 子句）结果是条件的 OR 运算，而不是 AND 运算）。
=SUMIF(收入!AJ2:AJ6400,Sheet2!A2, 收入!G2:G6400)The way I ended up getting past this was to create a column that combined the two columns I needed to check (ABCstatic, ABCemail, etc.) Doing it this way allowed me to use just one 'SUMIF' (using two SUMIF clauses linked together results in OR-ing of the conditions, rather than AND-ing).
=SUMIF(Revenue!AJ2:AJ6400,Sheet2!A2, Revenue!G2:G6400)我认为您不能将完整列与 SUMPRODUCT 一起使用，因此您必须选择一个足够大的范围。或者使用一些动态范围名称。
I don't think you can use full columns with SUMPRODUCT, so you'll have to pick a range sufficiently large. Or use some dynamic range names.