Hi - Dave here.
A key formula skill in Excel is using
mixed references, which lock specific parts of a reference. For example, the reference $A1 locks the column, but allows the row to change.
The worksheet below shows how mixed references can be used in an actual problem. The formula in cell G5 is:
=COUNTIFS(dept,$F5,group,G$4)
Here, the named ranges
dept and
group automatically behave like absolute references and will not change. The references to $F5 and G$4 however are mixed. As the formula is copied, the
row in $F5 changes and the
column in G$4 changes, which allows the COUNTIFS function to generate correct counts for all combinations of Department and Group:
[
download the workbook and read the full explanation]
This example also shows how to solve this problem with an Excel Table, which is a little tricky because of the syntax needed to lock the structured reference. The syntax looks like this:
=COUNTIFS(data[[Dept]:[Dept]],$F5,data[[Group]:[Group]],G$4)
The article explains both approaches and includes the worksheet. I also added an all-in-one dynamic array formula. Download the workbook and try it out yourself.
Excel formulas
We maintain a large list of working formulas
here.
If you need more structure, we also offer
video training.
Have a fun and safe weekend!
Dave
