## Thursday, August 29, 2013

Question:

( by amj05 )

If I have two columns that look like this:

 A X, Y B Y C X, Z B X, Y, Z D Z A Y, Z A X, Y, Z

Is there a formula to count the total number of times Y appears to the right of A (whether or not Y appears with anything else)? The only way I know involves way too many lines (especially since there will be more variables than in this example). Thank you!

Solution:

Have a look at the following screenshot:

I have the data in Column A and B.
And I have the following formula in Cell D1:
=query(A:B;"select A,count(B) where B contains 'Y' group by A label count(B) '' ";0)

And if you want to only count the number of times Y appears to the right of A, then I have the following formula in Cell G1:
=counta(iferror(query(A:B;"select A where A = 'A' and B contains 'Y' ";0)))

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,