COUNTING # OF PEOPLE IN LOCATIONS FROM LISTED NAMES

[Newbienew]

Hello,

I am looking to use a table that has people's names listed next to their locations. At the top, there is a selection box to select the area where the name member is located. From there, I want to calculate everyone in those location that has a cell filled in cell b5. Then non blanks in b6, totals in the B7. I attempted to nest a countifs index match formula but ended up crying. Please help

Project index countif.xlsx
	B	C	D	E	F	G	H
4	West	selection
5		blanks
6		non-blanks
7		total
8						name 1	West
9						name 2	East
10						name 3	North
11						name 4	South
12	name 1		1			name 5	North
13	name 2					name 6	South
14	name 3		1			name 7	East
15	name 4					name 8	East
16	name 5		1			name 9	West
17	name 6		1
18	name 7
19	name 8		1
20	name 9		1
21	name 1		1
22	name 2		1
23	name 3		1
24	name 4		1
25	name 5
26	name 6
27	name 7
28	name 8		1
29	name 9		1
30	name 1
31	name 2
32	name 3		1
33	name 4		1
34	name 5
35	name 6		1
36	name 7
37	name 8
38	name 9		1
39	name 1		1
40	name 2
41	name 3
42	name 4		1
43	name 5
44	name 6
45	name 7		1
46	name 8
47	name 9
48	name 1		1
Sheet1

 

[Ezguy4u]

New Let's get the ball rollin. We have to start somewhere so this is my solution to your problem. Now I know you didn't ask for a program but let's see if you can use this. If you have problems or questions let us know. Sometimes the A plus students weigh in and give us their solutions which are always better than mine. So let the discussion begin.

Sub Prog1()

Dim LastCel As Integer
Dim count1 As Integer
Dim count2 As Integer

LastCel = Cells(Rows.Count, "B").End(xlUp).Row

count1 = 0
count2 = 0

For i = 8 To 16

    If Range("B4") = Cells(i, 8) Then

        For a = 12 To LastCel

            If Cells(a, 2) = Cells(i, 7) Then
                count1 = Cells(a, 4) + count1
                count2 = count2 + 1
            End If
        Next a
    End If

Next i
Range("B5") = count2 - count1
Range("B6") = count1
Range("B7") = count2
Range("B5").Select

End Sub

23-09-30 work 1.xlsm
	B	C	D	E	F	G	H
4	West	selection		North
5	2	blanks		South
6	7	non-blanks		East
7	9	total		West
8						name 1	West
9						name 2	East
10						name 3	North
11						name 4	South
12	name 1		1			name 5	North
13	name 2					name 6	South
14	name 3		1			name 7	East
15	name 4					name 8	East
16	name 5		1			name 9	West
17	name 6		1
18	name 7
19	name 8		1
20	name 9		1
21	name 1		1
22	name 2		1
23	name 3		1
24	name 4		1
25	name 5
26	name 6
27	name 7
28	name 8		1
29	name 9		1
30	name 1
31	name 2
32	name 3		1
33	name 4		1
34	name 5
35	name 6		1
36	name 7
37	name 8
38	name 9		1
39	name 1		1
40	name 2
41	name 3
42	name 4		1
43	name 5
44	name 6
45	name 7		1
46	name 8
47	name 9
48	name 1		1
Data

 

[BigBeachBananas]

How about using helper columns and COUNTIFS.

Book1.xlsx
	A	B	C	D	E	F	G	H
1	South	selection
2	3	blanks
3	5	non-blanks
4	8	total
5						name 1	West
6						name 2	East
7						name 3	North
8						name 4	South
9	name 1		1			name 5	North
10	name 2					name 6	South
11	name 3		1			name 7	East
12	name 4					name 8	East
13	name 5		1			name 9	West
14	name 6		1
15	name 7						count non-blanks	count-blanks
16	name 8		1				0	0
17	name 9		1				0	0
18	name 1		1				0	0
19	name 2		1			name 4	3	1
20	name 3		1				0	0
21	name 4		1			name 6	2	2
22	name 5						0	0
23	name 6						0	0
24	name 7						0	0
25	name 8		1
26	name 9		1
27	name 1
28	name 2
29	name 3		1
30	name 4		1
31	name 5
32	name 6		1
33	name 7
34	name 8
35	name 9		1
36	name 1		1
37	name 2
38	name 3
39	name 4		1
40	name 5
41	name 6
42	name 7		1
43	name 8
44	name 9
45	name 1		1
Newbienew

Cell Formulas
Range	Formula
A2	A2	=SUM(H16:H24)
A3	A3	=SUM(G16:G24)
A4	A4	=SUM(A2:A3)
F16:F24	F16	=IF(G5:G13=A1,F5:F13,"")
G16:G24	G16	=COUNTIFS($A$9:$A$45,F16,$C$9:$C$45,1)
H16:H24	H16	=COUNTIFS($A$9:$A$45,F16,$C$9:$C$45,"")
Dynamic array formulas.