counting particular hierarchical column with condition

[phil1111]

I would like to count the number of 1s (on the left) and organize as shown on the right.
notice rows 1 and 2 are hierarchical.
in other words, something along the lines of "count soccer players in 2020 in Europe"

My attempt was to first match headers then count the column returned by INDEX, but I'm having trouble since there are 2 headers & a condition on column B to match against.
Any help will be appreciated. Much thanks!

 

[Toadstool]

Hi Phil1111,

If you'll repeat the sport for each row in column J then it's relatively straightforward:

Phil1111.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			North Am	North Am	Europe	Europe	Asia	Asia				North Am	Europe	Asia
2			2020	2021	2020	2021	2020	2021		tennis	2020	1	0	1
3	Mr. A	tennis	1				1	1		tennis	2021	0	0	1
4	Mr. B	soccer		1	1					soccer	2020	1	3	1
5	Mr. C	soccer	1		1	1	1			soccer	2021	1	2	0
6	Mr. D	soccer			1	1
7
Sheet1

Cell Formulas
Range	Formula
L2:N7	L2	=IF($K2="","",SUMPRODUCT(($C$3:$H$999>0)*($B$3:$B$999=$J2)*($C$2:$H$2=$K2)*($C$1:$H$1=L$1)))

If you want to omit the sport for those intermediate rows then it becomes a little messier:

Phil1111.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			North Am	North Am	Europe	Europe	Asia	Asia				North Am	Europe	Asia
2			2020	2021	2020	2021	2020	2021		tennis	2020	1	0	1
3	Mr. A	tennis	1				1	1			2021	0	0	1
4	Mr. B	soccer		1	1					soccer	2020	1	3	1
5	Mr. C	soccer	1		1	1	1				2021	1	2	0
6	Mr. D	soccer			1	1
7
2nd

Cell Formulas
Range	Formula
L2:N7	L2	=IF($K2="","",SUMPRODUCT(($C$3:$H$999>0)*($B$3:$B$999=INDEX($J$2:$J2,AGGREGATE(14,6,ROW($J$2:$J2)-ROW($J$1)/($J$2:$J2<>""),1)))*($C$2:$H$2=$K2)*($C$1:$H$1=L$1)))

 

[phil1111]

Toadstool,
thank you for your response!
I didn't know to use sumproduct like that.

While your solution worked perfectly for the demo data, I've encountered another problem:
The function returns #N/A.

I've tried looking for the cause, and it seems like something goes wrong when counting the sports:
1. counting 1s * sport: ($C$3:$H$999>0)*($B$3:$B$999=$J2)
this returned #N/A

2. counting 1s * year: ($C$3:$H$999>0)*($C$2:$H$2=$K2)
this returned the correct sum of 1s of 2020

3. counting 1s * region: ($C$3:$H$999>0)*($C$1:$H$1=L$1)
this returned the correct sum of 1s of North America

I've made sure there are no typos in sports and that the variable type is also the same.
Would you know what is the cause of returning NA?

Thanks for your help!

 

[Toadstool]

Toadstool,
thank you for your response!
I didn't know to use sumproduct like that.

While your solution worked perfectly for the demo data, I've encountered another problem:
The function returns #N/A.

I've tried looking for the cause, and it seems like something goes wrong when counting the sports:
1. counting 1s * sport: ($C$3:$H$999>0)*($B$3:$B$999=$J2)
this returned #N/A

2. counting 1s * year: ($C$3:$H$999>0)*($C$2:$H$2=$K2)
this returned the correct sum of 1s of 2020

3. counting 1s * region: ($C$3:$H$999>0)*($C$1:$H$1=L$1)
this returned the correct sum of 1s of North America

I've made sure there are no typos in sports and that the variable type is also the same.
Would you know what is the cause of returning NA?

Thanks for your help!

You're welcome.

SUMPRODUCT does not, in itself, return #N/A.
Given that only ($C$3:$H$999>0)*($B$3:$B$999=$J2) gives an error then somewhere in column B between rows 3 and 999, or on the J column for the row which returns the error, there must exist already an #N/A error.

 

[phil1111]

Toadstool, thank you for your reply.
you were correct; there was indeed an NA in column B that I hadn't realized existed.
everything worked after turning the NA into blank cells.

thanks again for all your help!

 

[Toadstool]

You're welcome. Glad you found the challenge.