Rollup and count

[chitti_us]

How to count the list of employees rolling upto a manager.

[popipipo]

=IFERROR(VLOOKUP(B2,$A$2:$B$10,2,0),"")

Do you mean this??

[chitti_us]

Thank you Popipipo for your response,

In the table I was looking for answer which will translate to ...

Chris would have a roll up totaling to 8 reportees.
DEF would have a roll up totaling to 5 reportees.
MNO would have a roll up totaling to 2 reportees.

Finally Mike would have total roll up of employees 9 reportees.

does that make good example ?

[popipipo]

Is this better?

[chitti_us]

Popipipo,

The count is good, but only thing which I have Column A and B only. I do not have other column's, for representation sake I have kept that. And my real data is more than 20,000 rows.

Greatly appreciate your inputs.

Thanks,

[FlameRetired]

Edited:

Since your profile is Excel 2016 you do not have the SORT function.

In I2 you may have to array enter this formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

=INDEX($B$2:$B$10,MATCH(0,COUNTIF($B$2:$B$10,"<"&$B$2:$B$10)-SUM(COUNTIF($B$2:$B$10,I$1:I1)),0))

Then in J2 to get the counts.

=COUNTA(INDEX($B$2:$B$10,MATCH(I2,$B$2:$B$10,0)):INDEX($B$2:$B$10,MATCH("zzzz",$B$2:$B$10)))

[chitti_us]

Thank you at flame retired. This is what I was looking for.

When I tried to replicate the same using the formulas in the actual file with more than 23,000 rows, it is not working. The other system is having Microsoft Office 365 Version 2505.

Could you please explain me, how the formula works.

[FlameRetired]

....
When I tried to replicate the same using the formulas in the actual file with more than 23,000 rows, it is not working. The other system is having Microsoft Office 365 Version 2505.

In your next post please copy and paste the exact formula you are using that doesn't work.

[chitti_us]

In I2 you may have to array enter this formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

=INDEX($B$2:$B$10,MATCH(0,COUNTIF($B$2:$B$10,"<"&$B$2:$B$10)-SUM(COUNTIF($B$2:$B$10,I$1:I1)),0))

When I am copying the command from I2, to I3, I4, I5 etc., it is giving me the message " # N / A ".

[wk9128]

Office 365 pls try

=LET(m,B2:B10,n,UNIQUE(m),HSTACK(n,DROP(REDUCE("",n,LAMBDA(x,y,VSTACK(x,LET(pos,MATCH(y,m,0),COUNTA(m)-pos+1)))),1)))

[chitti_us]

Thank you very much wk9128, this formula did worked.

Take a bow to your knowledge.

Could you please explain how this works.

[wk9128]

@chitti_us You're Welcome. Glad to help . Thank You for the feedback and rep.

Hi chitti_us POST#03 said "Finally Mike would have total roll up of employees 9 reportees."

Although formula and code was achieved
Why isn't it 1? You need to explain clearly, why should I explain ?

VBA USER DEFINE CODE for Office 2016

=wk(B2:B10)

Function wk(rng As Range) As Variant
    Dim m As Variant
    Dim dict As Object
    Dim result() As Variant
    Dim i As Long, j As Long
    Dim count As Long, pos As Long
    Dim key As Variant
    Dim keysArray() As Variant
    m = rng.Value
    Set dict = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(m, 1)
        If Not dict.exists(m(i, 1)) And Not IsEmpty(m(i, 1)) Then
            dict.Add m(i, 1), 1
        End If
    Next i
    ReDim result(1 To dict.count, 1 To 2)
    keysArray = dict.keys
    For i = 0 To dict.count - 1
        result(i + 1, 1) = keysArray(i)
        pos = Application.Match(keysArray(i), m, 0)
        count = Application.CountA(rng) - pos + 1
        result(i + 1, 2) = count
    Next i
    wk = result
End Function

[FlameRetired]

@chitti_us

The reason I asked you to post the formula you are using when mine worked is that quite often formula range references are not correct.