Count of entries meeting criteria

[Karen McKenzie]

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.

 

[Ron Coderre]

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]

Works perfectly thanks!.

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)