My data looks something like (assume begins in A1):

```
Category,Label1,Label2
Dogs,blank,doe
Dogs,zoo,blank
Cats,zoo,yup
Cats,doe,hgg
Cats,blank,doe
```

What the result should look like:

```
Dogs, 2
Cats, 4
```

My actual data has 100s of rows and 5 label columns.

I'm looking for a solution that either uses sumproduct or an array formula and can handle blanks and arrays of different sizes. I would like to avoid VBA if possible.

I ended up with this formula but still not the right answer:

```
=SUM((($A$2:$A$6="Dogs")*($B$2:$C$6<>""))/(COUNTIF($B$2:$C$6,$B$2:$C$6&"")))
```

I've tried concatenating (A2:A6&B2:C6) in countif range parameter but countif didn't accept this. I've tried sumproduct but it didn't like that I was using arrays of different sizes (1 column vs 2 column) and much more. I've spent the better part of two days researching and trying to solve this.

Looking forward to your help and expertise.

`Cats`

result 4? – Michael Frank Oct 2 '15 at 1:37`100x100`

times – wbeard52 Oct 2 '15 at 1:50