Wednesday, August 8, 2012

yogi_Count Number Of Employees Base On Rejected Status For Distinct Versions

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #679   Aug 06, 2012     www.energyefficientbuild.com.

user Mark Tingson said: (http://productforums.google.com/forum/?zx=ngx4q4i3g39d#!category-topic/docs/spreadsheets/DxeaR6k_a8U%5B1-25%5D)
Formula to count values from a specific table

Hi Everyone,
I'm hoping that you can help me solve this problem.
Here's my table
A         B C D
Name Versions Employee Status
Campaign 1 version3 Mark Rejected
Campaign 1 version4 Mark Rejected
Campaign 1 version3 Mark Rejected
Campaign 1 version4 Mark Rejected
Campaign 1 version3 Mark Approved
Campaign 1 version4 Mark Approved
Campaign 2 version1 John Rejected
Campaign 2 version1 John Rejected
Campaign 2 version1 John Rejected
Campaign 2 version1 John Approved
Expected Result
Mark                 2
John                 3
Base on the table, I want to get the number of campaigns which has versions and were rejected from an Employee. Each campaigns has 1 or more Versions. It is replicated if the status were rejected.
I thought of using a combination of COUNTIF and QUERY but my results were wrong. I'm thinking of using FILTER but I don't know how to start.
Below is the wrong formula I made.
=COUNTIF(QUERY(A2:D8, "SELECT A,B,D  WHERE C = """&A11&""""), "Rejected")
Let me know if it makes sense.
Thanks in Advance!
Mark
-----------------------------------------------------------------------------------------
Sorry, I did not check Mark's proposed solution ... in any event following is a solution to the problem