CountIf summary using VBA

[ravikiran]

Hi Excel Gurus,

I need help with developing VBA code to get a CountIf summary statement from a table.

Requirements:

1. I have some data in a table with Name and Activities the person is involved. (there might be multiple activities for one person). There are two tags for each activity - Priority (High, Medium) and Status (Completed, In progress, Started).
2. This list is dynamic and keeps on modifying and extending and is available on a Locked worksheet in a locked workbook.
3. Need to fetch unique names and activities list.
4. And then fetch the count as in the summary table.
5. Sample table and summary table are added to the excel file uploaded.

Your help is much appreciated.

Thanks in advance.

cheers,
Ravi.

 

[Hui]

Why not simply use a Pivot Table

Setup the Data Area as a Table or Named Range so that it expands automatically

Insert a Pivot Table
In Rows insert Name and Activity
In Column add Priority and Status
Sum values: Count of Status

 

[ravikiran]

Thank you Hui. Got that solved.

cheers,
Ravi.