在使用COUNTIFS函数时,如何仅计算可见行? [英] How do I only count visible rows when using the COUNTIFS function?
问题描述
我一直在使用Excel的COUNTIFS函数来计算满足某些条件的表中的行数,例如:
= COUNTIFS(Table1 [Result],Fail,Table1 [Comments],)
现在我想修改这个表达式,以便它只计算Table1中可见的行。 (I.E.未过滤出来)我如何才能完成这个?
简单的方法是在表中添加另一列。称为 helper
,使用这样的公式
= SUBTOTAL(103,B2) code>
其中列B为结果列
现在将公式更改为
= COUNTIFS(Table1 [Result],Fail,Table1 [Comments],,Table1 [Helper],1)
小计公式仅在可见行上返回1
没有辅助列,您可以使用此公式
= SUMPRODUCT((Table1 [Result] =Fail)*(Table1 [Comments] =),SUBTOTAL(103,OFFSET [结果],ROW(表1 [结果]) - MIN(ROW(表1 [结果])),0,1,1)))
I've been using Excel's COUNTIFS function to count the number of rows in a table that meet certain criteria, E.g:
=COUNTIFS(Table1[Result],"Fail", Table1[Comments], "")
Now I want to modify this expression so that it only counts rows in Table1 that are visible. (I.E. Not filtered out.) How can I accomplish this?
Simple way is to add another column to table - e.g. called helper
with a formula like this
=SUBTOTAL(103, B2)
where column B is Result column
Now change formula to
=COUNTIFS(Table1[Result],"Fail", Table1[Comments], "",Table1[Helper],1)
the subtotal formula only returns 1 on visible rows
Without a helper column you can use this formula
=SUMPRODUCT((Table1[Result]="Fail")*(Table1[Comments]=""),SUBTOTAL(103,OFFSET(Table1[Result],ROW(Table1[Result])-MIN(ROW(Table1[Result])),0,1,1)))
这篇关于在使用COUNTIFS函数时,如何仅计算可见行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!