CODEWITHSUNDEEP
excel
Stuart Cruickshank
Stuart Cruickshank

Reputation: 65

Modify Countif rank formula so that matching results only count once

So I have this spreadsheet

enter image description here

I am trying to workout the most picked item out of each bin based on the style and colour, I have created a sumif formula that counts and adds all of the items that is the same colour. I am now trying to create a ranking for each item based upon the Style Color Pick QTY and the month.

=(COUNTIFS(A:A,[@[bin-name]],I:I,[@Month],K:K,">"&[@[Style Color Pick QTY]])+1)

The problem is that it ranks every individual line, so instead of PS-1 Scarlet having a rank of 7 I would like it to have a rank of 2.

I have tried adding extra conditions to this such as an extra sumif that checks the previous result, or putting them in IF statements to compare to other results but all of my attempts result all Ranks being 1 or not changing.

So I am looking to modify this formula or being able to write a =unique() formula in another sheet and run the Rank Countif formula against that, I am able to write this but not sure how how to embed the # to make the formula dynamic(i.e. So i'm not dragging the formula down 1000 lines).

Any help would be appreciated.

Upvotes: 1

Views: 106

Answers (1)

riskypenguin
riskypenguin

Reputation: 2199

I found an unelegant solution to your problem. This approach might be able to be refactored into a more elegant solution though.

Small example

Column Rank is the column you already have.

Column Uniques is a helper column that has the formula =IFERROR(INDEX([Rank]; MATCH(0; INDEX(COUNTIF($B$1:B1;[Rank]); 0; 0); 0)); "") in its first cell B2.

Getting the Proper_Rank is now as easy as =RANK([@Rank];[Uniques];1).

The two helper columns Rank and Uniques can of course be hidden or placed wherever you want. As I said, it's probably possible to combine these helper columns into one calculation, but I don't know how.

Upvotes: 1

Related Questions