Counting unique records with additional criteria
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, |
can't you use a Pivot Table?
Lenny |
I would prefer to see if there's a formula that would work on this
example. |
Try...
=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A999,ROW(A2:A999)-ROW(A2),0,1)),--(A2:A9 99="Open")) Hope this helps! In article .com, wrote: 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, |
Worked like a charm...thanks much Domenic!
|
All times are GMT +1. The time now is 06:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com