Explain Countif function

**consultant_** · 06-21-2012, 04:21 PM

Originally Posted by DonkeyOte

Given you have XL2007 you can use COUNTIFS as opposed to SUMPRODUCT

=COUNTIFS($C$1:$C$12,$C1,$B$1:$B$12,">"&$B1)+1

However, though more efficient it is not (unlike SUMPRODUCT) backwards compatible with earlier versions of XL.

Can you please explain to me the logic around how you built up the Countif function? Thanks!

**NBVC** · 06-21-2012, 04:46 PM

COUNTIFS(criteriarange1,criteria1,criteriarange2,criteria2,....)

So we check two ranges against 2 respective criteria, and counts number of times both criteria are true in same rows.

First criteria checks number of times there is a match to C1 in all of C1:C12, the second checks if there any value in B1:B12 is greater than value in B1... after the count is made, we add 1 so that smallest rank (or largest value) value for each column A will be 1.

As you copy formula down, C1 and B1 becomew C2 and B2 and those checks are made again...

Explain Countif function

LinkBack

Thread Tools

Rate This Thread

Display

Explain Countif function

Re: using RANK and IF together

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions