如何根据Excel工作表中应用的列过滤器获取行数 [英] How to get the count of rows based on column filters applied in Excel sheet
问题描述
我有一个场景，
有一个Excel表格的缺陷列表，下面的列，
1. ID
2.缺陷描述
3.分配给
4.状态
5.预计关闭日期
假设表格中有100条记录。
在这些列中，我将对Status和Assigned应用过滤器到列
对于Ex：选择状态为打开并分配为Mathi
当过滤器应用于100个过程中时，仅过滤20个记录。 />
当我过滤它时，我会得到这个计数。
除了过滤缺陷列表表，每当更新缺陷列表表时，我想要摘要将在另一张表格中更新。
假设有另一张名为Summary的表格，其中有以下栏目
分配给
开放状态下的总缺陷数
In Progre中的总缺陷数ss state
关闭状态下的总缺陷数
我不想使用数据透视表，而是我想要一个公式来更新针对分配给的单元格 - > Mathi（静态值）打开状态下的总缺陷数 - > （在缺陷列表表中计算过滤记录计数的公式）
没有数据透视表是否可行。
感谢和问候，
Mathi。
Hi,
I have a scenario,
There is an Excel sheet for defects list with the below columns,
1. ID
2. Defect Description
3. Assigned To
4. Status
5. Expected Closure Date
Say there are 100 records in the sheet.
Among these columns I will apply filter on Status and Assigned To columns
For Ex: Select Status as "Open" and Assigned to as "Mathi"
When the filter is applied among the 100 only 20 records are filtered.
This count I will get when I filtered it.
Instead of filtering the defects list sheet, whenever the defects list sheet is updated I want the summary to be updated in a different sheet.
Say there is another Sheet called Summary in that there are columns as below
Assigned To
Total Defects Count in Open State
Total Defects count in In Progress state
Total Defects count in Closed state
I don't want to use pivot table, instead I wanted a formula to update the cell against Assigned To --> Mathi(static value) Total Defects count in Open State --> (A formula to calculate the filtered record count in the Defects List sheet)
Is it possible without a pivot table.
Thanks & Regards,
Mathi.
推荐答案
看起来你想用一组数据做很多事情。
在我看来过滤器方法是错误的。
我的理解是你想要用不同的条件进行多次计数。 br />
您应该查看SUMIF（）Excel功能，它可能符合您的需求。
Looks like you want to do many things with a set of data.
Looks to me the filter approach is a wrong one.
My understanding is that you want to do multiple counting with different conditions.
You should looks atSUMIF()Excel function, it may fit your needs.
这篇关于如何根据Excel工作表中应用的列过滤器获取行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
