Count spilled range result in one cell..

[kikola123]

Hello friends I have formula: =UNIQUE(FILTER($H$4:$H$264, $G$4:$G$264=I2)) and I need to count it so the result was 3. How is it possible?

Thanks in advanced.

 

[Peter_SSs]

Suppose your formula above is in cell J2, then try this formula in another cell.

=ROWS(J2#)

 

[kikola123]

Thank you. Is there a possibility for the outcome to reside exclusively in cell J2 without using on additional cells?

 

[kikola123]

I reach solution: =COUNTA(UNIQUE(FILTER($H$4:$H$264,$G$4:$G$264=I2))), but could you give me answer with older versions? So that same result was reached..

 

[Peter_SSs]

I reach solution: =COUNTA(UNIQUE(FILTER($H$4:$H$264,$G$4:$G$264=I2))), but could you give me answer with older versions? So that same result was reached..

Sorry, I did not realise that you wanted to actually replace the earlier formula. Note that your formula (& my previous suggestion too) will only work if there is always at least one cell in G4:G264 that contains the same value as I2. Will that always be the case for you?

could you give me answer with older versions?

Try this (also works if no cells in column G match I2)

=SUMPRODUCT(--($G$4:$G$264=I2),--(MATCH($G$4:$G$264&"|"&$H$4:$H$264,$G$4:$G$264&"|"&$H$4:$H$264,0)=ROW($G$4:$G$264)-ROW(G$4)+1))

 

[kikola123]

Thanks it works great.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.