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