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!