Hi,
I need your help.
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. I don't want to create a UDF in VBA.
The closest I got is with this formula which got me .83: SUM((($A$2:$A$6="Dogs")*($B$2:$C$6<>""))/(COUNTIF($B$2:$C$6,$B$2:$C$6&"")))
I've spent the better part of the day researching this and trying to solve this. I've found solutions that were close to what I was looking for but was not able to successfully modify them.
Looking forward to your help.
Thanks
Dan
Bookmarks