How to get the count of rows based on column filters applied in Excel sheet

See more:

MS-Excel

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.