# Trying to simplify a SORT(UNIQUE(FILTER(xxxxx))) , a COUNTIFS, and an INDEX(SORT(xxxx))

1. ## Trying to simplify a SORT(UNIQUE(FILTER(xxxxx))) , a COUNTIFS, and an INDEX(SORT(xxxx))

My main four formulas are:
G9: =SORT(UNIQUE(FILTER(Subdivision,Subdivision<>"")))
H9: =COUNTIFS(Subdivision,G9,New_Construction,H\$3,Status,H\$4,Days_Closed,"<"&H\$5)
I9: =INDEX(SORT(G9:H100,2,-1,FALSE),SEQUENCE(COUNTIF(H9:H100,">0")),{1,2})

I have named ranges Subdivision, New_Construction, Status, and Days_Closed. The formula in H9 is copied down the column. I'd like to reduce the multiple formulas to one formula, and the four columns down to two.

A simplified example is attached.
Test of Sort Unique and Filter and getting occurances count.xlsm  Register To Reply

2. ## Re: Trying to simplify a SORT(UNIQUE(FILTER(xxxxx))) , a COUNTIFS, and an INDEX(SORT(xxxx)

Maybe:

G9:
Formula:  `Please Login or Register  to view this content.`

I9:
Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: Trying to simplify a SORT(UNIQUE(FILTER(xxxxx))) , a COUNTIFS, and an INDEX(SORT(xxxx)

Note that the formula in I9 is independent of the formula in G9. It is, essentially, the same formula but with the output tweaked.  Register To Reply

4. ## Re: Trying to simplify a SORT(UNIQUE(FILTER(xxxxx))) , a COUNTIFS, and an INDEX(SORT(xxxx)

Thanks. That's an improvement. The main benefit with your formula is that I don't need to drag the formula I have in H9 down thousands of rows.  Register To Reply

5. ## Re: Trying to simplify a SORT(UNIQUE(FILTER(xxxxx))) , a COUNTIFS, and an INDEX(SORT(xxxx)

That's an improvement.
What's the phrase I'm looking for? Damned with faint praise?

The second formula, unless I am mistaken, does exactly what you said you wanted. Hence the reason for saying it i not dependent on the first. One formula, two columns, final result … 3 rows.  Register To Reply

6. ## Re: Trying to simplify a SORT(UNIQUE(FILTER(xxxxx))) , a COUNTIFS, and an INDEX(SORT(xxxx)

Yes, you are right. I didn't realize that at first, but then I spent some time understanding what you had and I realized I only needed the second formula. I've incorporated that into my big version of the workbook. It's working good. (The LET function is new to me!). Thanks.  Register To Reply

7. ## Re: Trying to simplify a SORT(UNIQUE(FILTER(xxxxx))) , a COUNTIFS, and an INDEX(SORT(xxxx)

You're welcome. Thanks for the rep. LET is a function that allows you to declare a variable and then assign a value, a cell, a range, or a formula to it. It can dramatically reduce the length of a complex formula and make it more readable. I try to use short variable names that relate to the range and/or the formula. Here, g was the filtered subdivision (which happened to be in column G. cg was the Count of g. hs was HSTACK, and so on.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 