Count of total requests assigned

[Jonam711]

Sorry guys for the blurred image. I need count the total assignings for employees based on the first table. There are two different categories (requests and tickets) and the total of both has to be derived in the total assigned column in second table. Thanks guys!

 

[mumps]

Try:

Sub CountRequests()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, dic As Object, k As Variant, rng As String
    v = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        If Not dic.exists(Range("A" & i + 1).MergeArea.Address(0, 0)) Then
            dic.add Range("A" & i + 1).MergeArea.Address(0, 0), Nothing
            Cells(Rows.Count, "F").End(xlUp).Offset(1) = v(i, 1)
            rng = Range("A" & i + 1).MergeArea.Offset(, 1).Resize(6, 2).Address(0, 0)
            Cells(Rows.Count, "G").End(xlUp).Offset(1) = WorksheetFunction.CountA(Range(rng))
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[Jonam711]

Try:

Sub CountRequests()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, dic As Object, k As Variant, rng As String
    v = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        If Not dic.exists(Range("A" & i + 1).MergeArea.Address(0, 0)) Then
            dic.add Range("A" & i + 1).MergeArea.Address(0, 0), Nothing
            Cells(Rows.Count, "F").End(xlUp).Offset(1) = v(i, 1)
            rng = Range("A" & i + 1).MergeArea.Offset(, 1).Resize(6, 2).Address(0, 0)
            Cells(Rows.Count, "G").End(xlUp).Offset(1) = WorksheetFunction.CountA(Range(rng))
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Thank you very much. It can't be derived with formulas? As my office people prefer it.

 

[Cubist]

The merged cells make the formula more complicated but try:

Book2
	A	B	C	D	E	F
1
2	Jack	1			Jack	6
3		1			Nick	3
4		1			Rick	1
5			1
6			1
7			1
8	Nick		1
9			1
10		1
11
12
13
14	Rick
15		1
16
17
18
19
Sheet3

Cell Formulas
Range	Formula
F2:F4	F2	=MAP(E2:E4,LAMBDA(m,SUM(--(FILTER(B2:C19,SCAN("",A2:A19,LAMBDA(a,b,IF(b="",a,b)))=m)<>""))))
Dynamic array formulas.

 

[Jonam711]

The merged cells make the formula more complicated but try:

Book2
	A	B	C	D	E	F
1
2	Jack	1			Jack	6
3		1			Nick	3
4		1			Rick	1
5			1
6			1
7			1
8	Nick		1
9			1
10		1
11
12
13
14	Rick
15		1
16
17
18
19
Sheet3

Cell Formulas
Range	Formula
F2:F4	F2	=MAP(E2:E4,LAMBDA(m,SUM(--(FILTER(B2:C19,SCAN("",A2:A19,LAMBDA(a,b,IF(b="",a,b)))=m)<>""))))
Dynamic array formulas.

Thanks but we won't be mentioning the numbers. We update the request number and need the count of it. In the formula provided I see there is SUM

 

[Cubist]

Thanks but we won't be mentioning the numbers. We update the request number and need the count of it. In the formula provided I see there is SUM

It's summing booleans. Did you try it?

 

[Jonam711]

I get #spill

 

[Cubist]

Clear out the cells below. It's a spill formula.

 

[Jonam711]

Clear out the cells below. It's a spill formula.

It worked, awesome. Let's say the cells are not merged and the emp name stays in the first cell of their request assigned how the formula looks plesse

 

[Cubist]

It worked, awesome. Let's say the cells are not merged and the emp name stays in the first cell of their request assigned how the formula looks plesse

It'd be simpler if each row had a name, but it'd be similar to the merged cells.

Book2
	A	B	C	D	E	F	G
1						Drag down	Spill formula
2	Jack	A			Jack	6	6
3		B			Nick	3	3
4		C			Rick	1	1
5			D
6			E
7			F
8	Nick		G
9			H
10		I
11
12
13
14	Rick
15		J
16
17
18
19
Sheet3

Cell Formulas
Range	Formula
G2:G4	G2	=MAP(E2:E4,LAMBDA(m,SUM(--(FILTER(B2:C19,SCAN("",A2:A19,LAMBDA(a,b,IF(b="",a,b)))=m)<>""))))
F2:F4	F2	=SUM(--(FILTER($B$2:$C$19,SCAN("",$A$2:$A$19,LAMBDA(a,b,IF(b="",a,b)))=E2)<>""))
Dynamic array formulas.

Book2
	A	B	C	D	E	F	G
1						Drag down	Spill formula
2	Jack	A			Jack	6	6
3	Jack	B			Nick	3	3
4	Jack	C			Rick	1	1
5	Jack		D
6	Jack		E
7	Jack		F
8	Nick		G
9	Nick		H
10	Nick	I
11	Nick
12	Nick
13	Nick
14	Rick
15	Rick	J
16	Rick
17	Rick
18	Rick
19	Rick
Sheet3

Cell Formulas
Range	Formula
G2:G4	G2	=MAP(E2:E4,LAMBDA(m,SUM(--(FILTER(B2:C19,A2:A19=m)<>""))))
F2:F4	F2	=SUM(--(FILTER($B$2:$C$19,$A$2:$A$19=E2)<>""))
Dynamic array formulas.