Count across a data set or index

[savvysum]

Hi, I need a count formula that counts names across multiple columns. I need to know how many people attended Philadephia. The answer is 5. Then pull the formal down to know how many attended Orlando = 6.
please note the blank cells are blank b/c there is a formula iferror return ""

A1 first cell

Philadelphia	Orlando
John Smith	Rafael
Jane doe	John Smith
	Darren
jimmy
stan	Phillips
nate	John
	nate
lookup	answer
Philadelphia	5
Orlando	6

 

[awoohaw]

Try:

Book1
	A	B	C
1
2	A1 first cell
3	Philadelphia	Orlando
4	John Smith	Rafael
5	Jane doe	John Smith
6		Darren
7
8	jimmy
9	stan	Phillips
10	nate	John
11		nate
12
13	lookup	answer
14	Philadelphia	5	5
15	Orlando	6	6
Sheet5

Cell Formulas
Range	Formula
C14:C15	C14	=SUMPRODUCT((A14=$A$3:$B$3)*($A$4:$B$11<>""))

 

[Peter_SSs]

Then pull the formal down

No need to even pull this one down. Just put the formula in B12 (for my layout) & both results will appear.

24 01 16.xlsm
	A	B
1	Philadelphia	Orlando
2	John Smith	Rafael
3	Jane doe	John Smith
4		Darren
5
6	jimmy
7	stan	Phillips
8	nate	John
9		nate
10
11	lookup	answer
12	Philadelphia	5
13	Orlando	6
Count attendees

Cell Formulas
Range	Formula
B12:B13	B12	=BYROW(A12:A13,LAMBDA(r,SUM(FILTER(--(A2:B9<>""),A1:B1=r))))
Dynamic array formulas.

 

[savvysum]

Try:

Book1
	A	B	C
1
2	A1 first cell
3	Philadelphia	Orlando
4	John Smith	Rafael
5	Jane doe	John Smith
6		Darren
7
8	jimmy
9	stan	Phillips
10	nate	John
11		nate
12
13	lookup	answer
14	Philadelphia	5	5
15	Orlando	6	6
Sheet5

Cell Formulas
Range	Formula
C14:C15	C14	=SUMPRODUCT((A14=$A$3:$B$3)*($A$4:$B$11<>""))

thank you that works!

 

[savvysum]

No need to even pull this one down. Just put the formula in B12 (for my layout) & both results will appear.

24 01 16.xlsm
	A	B
1	Philadelphia	Orlando
2	John Smith	Rafael
3	Jane doe	John Smith
4		Darren
5
6	jimmy
7	stan	Phillips
8	nate	John
9		nate
10
11	lookup	answer
12	Philadelphia	5
13	Orlando	6
Count attendees

Cell Formulas
Range	Formula
B12:B13	B12	=BYROW(A12:A13,LAMBDA(r,SUM(FILTER(--(A2:B9<>""),A1:B1=r))))
Dynamic array formulas.

thank you that works!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.