Simplify Count formula to count cells meeting a criteria across an array of columns

[rugbyhubby]

I have a worksheet similar to below. in the two columns under "values >1" I am calculating the number of times a value in my data is >1 for that particular header. i have a solution for this which is extremely taxing as I have 6500+ rows of data across 16 columns. To calculate the ABC and DEF ">1" columns I am using the following:
{=COUNT(IF((MyUserID=$c3)*(MyHeader=a$2)*(MyDATA>1),MyDATA,""))}

SUMPRODUCT is also taxing so I was wondering if someone had another way of doing this calculation. I can get a Sum(Index.... to work but it's giving me a sum of the counts and not a count of the cells that are >1. Any ideas would be great.

A B C D E F G H I J
values >1 MyHeader
ABC DEF MyUserID ABC ABC ABC ABC DEF DEF DEF
3 1 123 2 1 7 3 2
0 0 456 1 1 1 1
1 2 789 2 1 2 2
3 0 1122 24 12 7 1
0 2 1455 9 5 1
3 1 1788 8 5 1 5 3
1 1 2121 2 1 1 1 1 4
0 3 2454 10 6 10

 

[Jonmo1]

Will your USERID appear only once in the MyUserID column ?

If YES, then you can do

=COUNTIFS(MyHeader,A$2,INDEX(MyData,MATCH($C3,MyUserID,0),0),">1")

 

[rugbyhubby]

that worked - i hadn't created my formula like that but apparently I had MyData (in my original sheet) one column longer than the actual data and that was causing me errors. thanks for the quick response!

 

[Jonmo1]

You're welcome.