How can we rank within groups in Excel? Most experts will tell you to use the SUMPRODUCT function. Why? It’s the only solution they know or maybe they’re concerned about compatibility. Is anyone still using Excel 2003? Let’s compare SUMPRODUCT and COUNTIFS.

Ranking Within Groups

We want separate ranks for Aristole and Richard Pryor. Here are two solutions:

=SUMPRODUCT(($A$4:$A$12=$A4)*($B$4:$B$12>=$B4))

=COUNTIFS($A$4:$A$12,$A4,$B$4:$B$12,”>=”&$B4)

Download my Excel file here to see how to do this.

Why SUMPRODUCT?

Most recommend using the SUMPRODUCT function. Why? Maybe it evolved like this…

Not as intense as the vlookup vs index/match debate but still interesting to compare:

COUNTIFS

• It’s so simple to use. Type =COUNTIFS(   The screen tip guides you through the arguments.
• Faster?! On big datasets you might notice it’s faster than SUMPRODUCT.
• Compatibility. These days it’s not a major concern but ask first (what version do you have?).

SUMPRODUCT

• Initial time investment in learning this versatile/powerful function is worth it!
• There are no compatibility concerns with this dude. He groves across the decades!
• Might be slower on big datasets.
• Novice users may struggle to understand (it is kind of funky)

What Is My Preference?

I used SUMPRODUCT for years due to potential compatibility problems (xlfn error). But now it’s 2018! I use COUNTIFS 🙂

About Me

My name is Kevin Lehrbass. I’m a Data Analyst.

This is my personal blog about Microsoft Excel.

Fenton is on the left and Cali is on the right.

Fenton likes to keep things simple. He prefers COUNTIFS.

Cali is more ambitious and prefers SUMPRODUCT.

Visit my recommended Excel training!