Count of entries meeting criteria
 

I have a spreadsheet set up with a filter in column AB so I can filter on
individual companies. Column AA then contains multiple status references ie
new, scrap, cascade

At the bottom of the sheet I want to be able to count how many of each
status from column AA we have for the company filtered in column AB.

Could someone please help.

Count of entries meeting criteria
 

I believe a Pivot Table would be more suited to your situation that formulas
but if you MUST use formulas...

This formula returns the filtered count of AA2:AA30 items that equal
"FRISBEE":

=SUMPRODUCT(SUBTOTAL(3,OFFSET(AA2,ROW(2:30)-2,))*(AA2:AA30="FRISBEE"))

Adjust range references to suit your situation.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Karen McKenzie" wrote in message
...
I have a spreadsheet set up with a filter in column AB so I can filter on
individual companies. Column AA then contains multiple status references
ie
new, scrap, cascade

At the bottom of the sheet I want to be able to count how many of each
status from column AA we have for the company filtered in column AB.

Could someone please help.

Count of entries meeting criteria
 

Works perfectly thanks!.



"Ron Coderre" wrote:

I believe a Pivot Table would be more suited to your situation that formulas
but if you MUST use formulas...

This formula returns the filtered count of AA2:AA30 items that equal
"FRISBEE":

=SUMPRODUCT(SUBTOTAL(3,OFFSET(AA2,ROW(2:30)-2,))*(AA2:AA30="FRISBEE"))

Adjust range references to suit your situation.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Karen McKenzie" wrote in message
...
I have a spreadsheet set up with a filter in column AB so I can filter on
individual companies. Column AA then contains multiple status references
ie
new, scrap, cascade

At the bottom of the sheet I want to be able to count how many of each
status from column AA we have for the company filtered in column AB.

Could someone please help.