Hello,
I'm trying to use Countifs to compare each cell to multiple columns, sometimes involving calculations within those comparisons. I'm trying to find out who earns less than a specified differential per year of service. I've attached my Excel file and will explain it here:
For example, the first entry is Alpha A (job title and grade) and earns $3750 with 11 years of service. The second entry is also an Alpha A but earns $3700 with only 5 years of service. As specified in H2, each year of service should bring a salary differential of 1%. So the first Alpha A should be earning at least 6% (1% X 6 years) more than the second Alpha A, but actually earns only 1.35% ($50) more. So the count in F2 should be "1" meaning there is one person who earns more than that individual based on the rule (1% per years of service).
Here's what my formula looks like in theory (obviously doesn't work because I'm subtracting with ranges): countifs($A:$A,$A2,$B:$B,$B2,$D:$D>$D2,$C:$C>($C2/($H$2*($E2-$E:$E))))
I'm trying to avoid array formulas because my file is very large. I'm not sure if Countif is even the best formula to be using.
Thank you!
Bookmarks