A simple way to do this is with the COUNTIFS function.
COUNTIFS function
TheCOUNTIFS functionis designed to count things based on more than one condition.
Forcriteria1, we use themixed reference$F5.
Cell F5 contains the value “Engineering”, which will be used for criteria.
We leave the row relative because we want the row to change as we copy the formula down.
Forcriteria2, we use themixed referenceG$4.
Cell G4 contains the value “A”, which will be used for criteria.
This is the final formula entered in cell G5, and copied into the range G5:H8.
To recap, the named rangesdeptandgroupautomatically behave like absolute references and will not change.
The references to $F5 and G$4 however are mixed.
Excel Tables usestructured references, which appear automatically in formulas that refer to them.
One of the videos shows an easy way to lock a structured reference.
If you are new to Excel Tables, seeWhat is an Excel Table?
TheLET functionis used to assign all three intermediate results to the variablesdepts,groups, andcounts.
Next, theHSTACKandVSTACKfunctions are used to assemble the final table.
To learn more about how to convert a formula to use LET, see thisdetailed LET function example.
As a bonus, there is no need to lock specific cell references.
Seethis examplefor another related problem with a more complete walkthrough.
