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:
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.