Counting unique records with additional criteria

[mike.wilson8]

I have a list of unique records in a spreadsheet. Each record has a
value of either "open" or "closed". I can get the total number of
unique records by doing a = SUBTOTAL(3,A2:A999), however I need a
similar formula that can count the number of unique records + count the
number of records either with an "open" or "closed" status (all in one
entry). Ex - = SUBTOTAL(3,A2:A999, ="OPEN")

Any ideas?

Thanks,

 

[Lenny]

can't you use a Pivot Table?
Lenny

 

[mike.wilson8]

I would prefer to see if there's a formula that would work on this
example.

 

[Domenic]

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A999,ROW(A2:A999)-ROW(A2),0,1)),--(A2:A9
99="Open"))

Hope this helps!

 

[mike.wilson8]

Worked like a charm...thanks much Domenic!