| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I count wildcard text meeting certain criteria in EXCEL? | Excel Worksheet Functions | |||
| Counting distinct entries based on meeting month & year criteria | Excel Worksheet Functions | |||
| count records meeting three criteria | Excel Worksheet Functions | |||
| I Need to Count Number of Entries Based on Two Criteria | Excel Worksheet Functions | |||
| COUNTIF MEETING TWO CRITERIA eg>5 AND <10.1 | Excel Worksheet Functions | |||
Linear Mode
