CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
Never Enough Books
Never Enough Books

Reputation: 1

Rank and Array Formulas In Google Sheets based on criteria from two columns

I am struggling to rank two columns with an array formula:

The current formula: 

=if(B2<>"",if(B2=B1,if(C1=1,2, C1+1),1),)

Here is a link to the Google Sheet sample:

https://docs.google.com/spreadsheets/d/14BX3mO2Nd-RTMyhivwJ9w_rnVbVjSqE72o3Rdx-GQME/edit?usp=sharing

I'd like to be able to create an array formula in Column C but multiple attempts has me stumped if that's possible. Essentially Column C should be ranked in order of lowest $ to highest $ from column A and the order in which it appears so rank values do not repeat. Additionally, the rank should only be the same item in column B and different for each item.

Upvotes: 0

Views: 1500

Answers (2)

MattKing
MattKing

Reputation: 7783

This should work if put in C1:

=ARRAYFORMULA({"Rank";IF(B2:B="",,COUNTIFS(B2:B,B2:B,A2:A+1/(1000*ROW(A2:A)),"<="&A2:A+1/(1000*ROW(A2:A))))})

Upvotes: 0

user11982798
user11982798

Reputation: 1908

You can rank your data like this:

=arrayformula(query({sort({row(A2:A),A2:B},3,1,2,1), sort({COUNTIFS(B2:B,B2:B,row(B2:B),"<=" & row(B2:B)),B2:B},2,1,1,1)}, "Select Col4 where Col4<>0 Order by Col1 "))

Upvotes: 0

Related Questions