Google Spreadsheet Countif with Arrayformula

Question:

( by Adrian Kabigting )

Not sure on how to approach this:

So here's how my first spreadsheet is setup

Column A - staff name

Column B - certification boolean (Y or N)

Now in a different worksheet, I am distributing the staff into 2 different locations.  I'm simply just typing their names under a table with the location name as the header, i.e. A1="Location 1", B1="Location 2", A2="Joe"...

Now as I type names under Location 1, is there a formula to count how many people have the certification identified by the boolean value in the previous worksheet, column B?  Sort of like "count the names if they have the certification"

I understand COUNTIF (I think at least) but I don't know how to apply it to a range, checking if each cell value is contained in Column A of the first spreadsheet, and then checking if Column B contains "Y" and then counting them.

I hope this makes sense, any help would be appreciated!

Solution:

Have a look at the following screenshot:

Sheet1:

Sheet2:

I have the following formula in Cell B2:

=arrayformula(if(A2:A="";"";countif(if('Sheet1'!B2:B="Y";'Sheet1'!A2:A;"");A2:A)))

Also have a look at the following alternate way of getting the solution:

Sheet3:

I have the following formula in Cell A2:

=query('Sheet1'!A:B;"select A,count(B) where B='Y' group by A label count(B) '' ")

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