Google Spreadsheet Count instances in an array with conditions

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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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,

Kishan.

iGoogleDrive