One of the biggest differences between beginner and advanced Excel users is how they define criteria in formulas. Beginners often hard-code values directly into formulas, while experienced analysts use dynamic references that allow spreadsheets to adapt automatically as business requirements change.
This article explores the difference using a dataset of technology and data-related careers. Along the way, we will examine the COUNTIF function, logical testing with AND(), and why dynamic criteria make spreadsheets easier to maintain and scale.
The Dataset
Consider the dataset attached below containing job titles, years of experience, annual salaries, and bonus amounts.
These values are placed in worksheet cells so that they can be modified without changing any formulas and the hiring goals had be defined as seen in cell R2
Below I will go through both the traditional and professional/scalable approach on how to handle the COUNTIF with the respective attached screenshots.
The Traditional Hard-Coded Approach
A common approach is to place the criteria directly inside formulas.
QST 1 : Find the number of jobs whose experience meets the criteria of Job_experience <= 5?
For our case the traditional approach Formula would be;
=COUNTIF(range, "criteria")
=COUNTIF(C3:C12, "<=5")
ANSWER = 6
Below is a screenshot
Q2: Find the number of jobs whose annual salary meets the criteria of Annual_salary >=90000?
Similar to qst 1 the formula would be:
=COUNTIF(range, criteria)
=COUNTIF(D3:D12,">=90000")
Answer = 9
As shown in the screenshot below:
The problem
The issue with this traditional approach is that if the condition changes the calculated sheet values do not automatically update unless you manually find the cell where the values are and recalculate.
Below is a screenshot on both scenarios where the Job experience was changed to 6 and the salary was changed to 100,000:
With the changed conditions we expect the calculated values of the meet goals count to change but with the traditional approach they cant unless I change the formulas to =COUNTIF(C3:C12, "<=6") and =COUNTIF(D3:D12,">=100000") respectively
THE BETTER APPROACH - DYNAMIC CRITERIA
With this Instead of embedding the value directly as we did "traditionally" into the formula, we can reference the goal cell.
QST 1: Find the number of jobs whose experience meets the criteria of Job_experience <= 5' and without changing the formulaJob_experience <= 6`?
plaintextS3
In this we will reference the goal cell which in our case isfor the Job experience and concatenate it&with the criteria"<=`
Formula would be :
=COUNTIF(range, "operator"& reference_cell)
=COUNTIF(C3:C12, "<="&S3)
Answer = 6
when we change the reference cell condition to <=6 The formula will still remain but the calculate cell value would update without lifting a finger.
`
Attached below are both cases:
1. Initial condition with <=5
Keep eyes on the formula and the condition on job experience and compare with the one attached below when the condition is changed.
2. Changed the condition to <=6
To note is that the condition value changed to 6, the calculated cell value also was updated to 8 but the formula remained the same
QST 2: Find the number of jobs whose salary meets the criteria of Annual_salary >= 90000 and without changing the formula Annual_salary >= 100000?
`plaintext
scenario 1 Annual salary >= 90000
=COUNTIF(D3:D12,">="&S4)
Answer 1: 9
Scenario 2 Annual_salary >= 100000
=COUNTIF(D3:D12,">="&S4)
Answer = 8
`
Screenshots of both scenarios
Scenario 1
Scenario 2
Mistakes you will commit
Many users initially attempt the following:
`plaintext
=COUNTIF(D3:D12,">=S4")
`
However, Excel interprets everything inside quotation marks as text.
Rather than reading S4 as a cell reference, Excel treats it as the literal text "S4".
Using the ampersand(&) instructs Excel to combine the operator with the value stored in the referenced cell.
`plaintext
=COUNTIF(D3:D12,">="&S4)
`
Why Dynamic Criteria is Better
Consider what happens if management changes the maximum experience requirement from 5 years to 7 years.
With hard-coded formulas, every occurrence of the value 5 must be updated manually.
With dynamic formulas:
=COUNTIF(D3:D12,">="&S3)
only the value in s3 needs to be changed.
All dependent calculations update automatically.
This approach offers several advantages:
- Improved maintainability.
- Reduced risk of formula errors.
- Easier report updates.
- Better support for dashboards and interactive models.
- Greater scalability as datasets grow.
Key Takeaway
As a firm believer that “reasonable people disagree”, I can already guess the common pushback: the traditional approach is shorter and easier to remember — which is true.
But since when did we start choosing the easier path at the cost of scalability and maintainability?
If your Excel sheet can’t adapt to change, it’s not a spreadsheet… it’s a stubborn opinion in grid form.
That said, I’m still at LUXDEV getting better at this every day, and I promised to keep you updated on the journey. This article is part of that progress.
Feel free to check out my GitHub — that’s where most of the sauce drops.
Till next time.
Top comments (1)
My next article will probably be on logical testing with AND() wanted to add it here but I thought the article would be too long.