Forum Discussion
Distinct Count number of completed Goals when all Task statuses in Goals are marked as completed
Good Day Excel users, what approach and formula can i use to solve this challenge on my subject (Distinct Count number of completed Goals when all Task statuses in Goals are marked as completed ).
Below is a dummy table i have generated to guide you. Thanks
For example all tasks in Web goal has Status as Completed so it will count as 1 completed goal and not two.
Riny_van_Eekelen HansVogelaar Haytham Amairah Please i need this solution, thanks in anticipation.
Dummy Table:
This is the expected output:
See the attached version. I added a helper column to the first sheet.
I used formulas that will work in all versions of Excel. It would be easier in Excel 365.
9 Replies
One more option for the collection. It could be done with PivotTable if add data to data model creating it and use couple of DAX measures
Unique Goals := DISTINCTCOUNT ( Range[GOALS] ) Completed Unique := CALCULATE ( [Unique Goals], Range[Goal STATUS] = "Completed" ) + 0Resulting PivotTable
- Thank you
Could you attach a sample workbook? Thanks in advance.
Hi,
Please find this file. This will automatically update the results as you extend or change the main data.
Certain functions used in this will work only in Excel 365.
Thank you!!
