# counting particular hierarchical column with condition

#### phil1111

##### New Member
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!

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### Toadstool

##### Well-known Member
Hi Phil1111,

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

Phil1111.xlsx
ABCDEFGHIJKLMN
1North AmNorth AmEuropeEuropeAsiaAsiaNorth AmEuropeAsia
2202020212020202120202021tennis2020101
3Mr. Atennis111tennis2021001
4Mr. Bsoccer11soccer2020131
5Mr. Csoccer1111soccer2021120
6Mr. Dsoccer11
7
Sheet1
Cell Formulas
RangeFormula
L2:N7L2=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
ABCDEFGHIJKLMN
1North AmNorth AmEuropeEuropeAsiaAsiaNorth AmEuropeAsia
2202020212020202120202021tennis2020101
3Mr. Atennis1112021001
4Mr. Bsoccer11soccer2020131
5Mr. Csoccer11112021120
6Mr. Dsoccer11
7
2nd
Cell Formulas
RangeFormula
L2:N7L2=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

##### New Member
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

##### Well-known Member
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

##### New Member
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

##### Well-known Member
You're welcome. Glad you found the challenge.

Replies
3
Views
284
Replies
10
Views
490
Replies
0
Views
338
Replies
2
Views
338
Replies
1
Views
350

Threads
1,191,120
Messages
5,984,762
Members
439,909
Latest member
daigoku

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back