I want to count IF both condition are true Logically count by AND excel

I have two Columns both are categorical columns. Like Age_group and Engagement_category. And I want to get count no. of each engagement_category in each Age_group.

| Engagement_category | Age_group |
|:-------------------:|:---------:|
| Nearly Engaged      | 21-26     |
| Not Engaged         | 31-36     |
| Disengaged          | 36-41     |
| Nearly Engaged      | 21-26     |
| Engaged             | 21-26     |
| Engaged             | 26-31     |

And Output as

| Age_group | Engaged | Nearly Engaged | Not Engagaged | Disengaged |
|:---------:|:-------:|----------------|---------------|------------|
| 21-26     | 1       |                |               |            |
| 26-31     |         |                |               |            |
| 31-36     |         |                |               |            |
| 36-41     |         |                |               |            |
| 41-46     |         |                |               |            |
| 46-51     |         |                |               |            |

Thank you for your time and consideration.

Solution 1:

You can use a Pivot Table:

• Age_Group --> Rows
• Engagement_category --> Columns
• Engagement_category --> Values should default to the Count option
• Format and rename to suit

Data

Pivot Table Fields setup

Pivot Table

Solution 2:

You can use COUNTIFS:

=COUNTIFS($A:$A,E$1,$B:$B,$D2)

The key here is the correct use of absolute / relative references.