Count of a column when associated values on another column are on same status

[Prabhu]

Hi,

I have three columns: Objective, Results, Status

I want to calculate the count of Objectives only when all the associated "Results" have status "Completed"

Attached the screenshot.

@sunny_talwar  @swuehl  @amit_saini  @Anil_Babu_Samineni @Kushal_Chawda @Anonymous  @rwunderlich  @marcus_sommer 

[m_woolf]

=count({<Results = {'Completed'}>}Objective)

[Prabhu]

Hi, I want to count the objective only when all it's results has
"completed" status.

[m_woolf]

Did you try what I suggested?

[Prabhu]

Yes, it is counting the objective even if it has 1 result as Completed.
Objective should be counted only when all of it's results are Completed

[marcus_sommer]

Try it with:

sum(aggr(-(count({<Results = {'Completed'}>}Objective)=count(Objective)),Objective))

- Marcus

[Prabhu · Accepted solution]

Thanks Marcus, but your calculation is giving one extra count. 

The following calculation gives exact count: 

=COUNT(DISTINCT AGGR(if(count( [Product Key Result])=count({<Status={'COMPLETE'}>}Status), ONLY( [Product Objective])), [Product Objective]))

[Anil_Babu_Samineni]

@Prabhu You got the answer? If so, please mark yourself as correct answer. If not, Please free to ask.