# 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

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.`

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.

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.

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.

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.

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.

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