Need Count based on Multiple Criteria

I want to display the COUNT of Total Number of Resources based on their Designation and Grade in Grade table (column I) as shown in the attachment.

Appreciate your help in this regard.

Hi Suresh,

See the attached file which has been formulated as per your query. thanks.

Grade Wise Resource Count based on Designations.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi Suresh,

While Dili's approach will work for the exact values you have in your sample sheet, if the grades in your Designation table change, the formulas will no longer return proper results. It might be easiest to add a helper column onto the Resources table that looks up the Designation for each resource. So in D4 enter:

=VLOOKUP(C4,$E$4:$F$7,2,0)

Fill that down to D18. Then in I4 you can just use:

=COUNTIF($D$4:$D$18,H4)

Fill that down to I6.

you can try this formula in I4 copied down (array formula requires CSE)
=SUM(--(LOOKUP($C$4:$C$18,$E$4:$E$7,$F$4:$F$7)=H4))

but your data in designation table needs to be sorted ascending by the designation

Hi Suresh,

In I4 with just ENTER, then copy down.

=SUMPRODUCT(COUNTIF(C$4:C$18,E$4:E$7)*(F$4:F$7=H5))