Google Spreadsheet Query For Displaying Pie Chart

Question:

(by Matt)

Morning All,

I've been looking at this for the last hour and have managed to get myself quite confused.  I have a Spreadsheet that looks like this (example only):

This is data collected as a result of a Google Form Survey.  In it we asked users from the business "Have you ever downloaded apps from Google Play?".  The second question is "Did you find anything useful for work purposes?".  After sending out the questionnaire I realised that some users are answering 'No' to the first [required] question and then answering 'No' to the second [optional] question, which is affecting the results slightly

What I would like to do is create a pie chart which displays - where users have said 'Yes' they have downloaded apps from the apps store, how many of them found an app useful for business. Something, perhaps, along the lines of a field which contains COUNT 'Yes' in column B where column A also has a 'Yes'.  Also, COUNT 'No' in column B where column A also has a 'Yes'?

The responses to the survey are still coming in so I would like this to be dynamic.  I started creating a new sheet and tried to use Array formula to create a table form which to derive the pie chart, but have ended up getting very confused.

Can anyone point me in the right direction?

Thanks

Matt

Solution:

Make a new sheet, and in this sheet put the following formula in Cell A1:

=query('Sheet1'!A:B;"select B,count(A) where A='Yes' group by B order by B desc label B '',count(A) ''";0)

I am assuming that your first sheet's name is 'Sheet1', or else replace 'Sheet1' with your sheet's name.

Now you will get the result as you can see in the screenshot below:

And now you can insert pie chart, and thus you can show the data in form of a chart.

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