I have a row of text that look like this:
|cat F | dog | dog | cat F | cat M | cat M | dog | dog |
So either the cell is 'cat F', 'cat M' or 'dog'. They are grouped in pairs (hence the color coding).
What I want to do is, for each pair, find all those that are 'cat' in the 1st column (doesn't matter if 'F' or 'M'). Then for those that are cat, find which ones have 'dog' in the 2nd column. In the example above, there are 2 pairs that have cat in the 1st column....and out of that, only 1 has 'dog' in the 2nd column. And then dividing the 1 match with the 2 pairs, the result should give .5'
I prefer if there was a function that could use one cell to do it (even if nested), but if it has to to split up in 2 steps, that's ok.
This is what I have tried (to first find the match of 'cat' 'dog' pair):
=SUMPRODUCT(((MOD(COLUMN(A1:A8),2)=0)*(A1:A8="cat*"))*(MOD(COLUMN(A1:A8),2)=1)*(A1:A8="dog"))
doesn't work.
and SUMIF seems to take only 1 conditions.
If I break it up into steps, I can start finding the pairs with cat as the 1st column:
=SUMPRODUCT((MOD(COLUMN(A1:A8),2)=0)*(A1:A8="cat*"))
which works, but only gives me a number (2). This doesn't help because I need to know which 2 pairs they are to continue on with the calculations.
Bookmarks