Provide Count based upon criteria in Data along with unique row and header data

[rugbyhubby]

I have a data set in which I am trying to count how often, for a given category (column) a Person (row) has a number greater than 1 (data). I have named the category section "Header" and the person list "People" and named the data range "Data". This may be an easy question but I am getting baffled! I am looking for a formula and not VBA to get the answer. Here is a sample set of data

Column1	1083801	10838012	10838013	10838014	1151620	11516205	11516206	11516207
Person1
Person2	1				1			1
Person3
Person4		3			1		2	1
Person5						1
Person6
Person7	5	1	2
Person8
Person9				1
Person10
Person11
Person12							1	5

<colgroup><col><col><col span="3"><col><col span="3"></colgroup><tbody>
</tbody>

In this example Person 4 I would count once for the first category and once for the second category as they have a value of 3 and 2 respectively. For Person 7 I would count twice for category 1 only. What I need the formula to solve for is:
* looking at a particular person
* identify how often their value is >1 for each category (placement of these counts would be in a separate column, one value for each category.

Any help would be greatly appreciated.

 

[estevaoba]

Try this:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=SUMPRODUCT((People="Person7")*(Data>1))[/TD]
[/TR]
</tbody>[/TABLE]

 

[rugbyhubby]

Try this:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=SUMPRODUCT((People="Person7")*(Data>1))[/TD]
[/TR]
</tbody>[/TABLE]

I forgot to mention I was trying to avoid sumproduct as it was killing my system! I was using =SUMPRODUCT((Header=Catg1)*(data>1)*(Person=Pers1)) and any change to the sheet was causing my system to recalc for about 10 minutes. Apologies for not putting this out there at the start.