Pivot table grouping and return OK if condition using countif()>0
Hi community, I would need your help here please.
I have a big table with multiple column, However I am focused in 2 main columns, call “Control” & “Status”.
In total i have 100 rows in my table, however I can have the same “Control” repeated in multiple row. I have different type of “Status” that could be: Not Started; In Progress; Completed.
I would need to group each same “Control”, And return a “OK” if for each Control it does NOT have an associated “Not Started”;”In Progress”; means only “Completed” status apply. Otherwise, it will return “NOK”.
I have a built a pivot table to show this in a summary visualization:
My first try was:
1. Build a specific Pivot table, In rows i insert “Status”; in values I insert “Status” but I change the Value Field Settings as Count.
2. Now Insert calculated Field, with the following formula:
=if((countif(status=”Not Started”)+countif(status=”In Progress”))>0;”NOK”;”OK”)
When I click ok, it returns “Too few”.
I don’t find the way to do it…
Can I anyone help me out to solve this need? How would you do it? Is this the best way?
I would need a smart and fast way idea.
Thank you a lot!
BR, Charlie1992
Hi community, I would need your help here please. I have a big table with multiple column, However I am focused in 2 main columns, call “Control” & “Status”.In total i have 100 rows in my table, however I can have the same “Control” repeated in multiple row. I have different type of “Status” that could be: Not Started; In Progress; Completed. I would need to group each same “Control”, And return a “OK” if for each Control it does NOT have an associated “Not Started”;”In Progress”; means only “Completed” status apply. Otherwise, it will return “NOK”. I have a built a pivot table to show this in a summary visualization: My first try was: 1. Build a specific Pivot table, In rows i insert “Status”; in values I insert “Status” but I change the Value Field Settings as Count.2. Now Insert calculated Field, with the following formula: =if((countif(status=”Not Started”)+countif(status=”In Progress”))>0;”NOK”;”OK”) When I click ok, it returns “Too few”.I don’t find the way to do it… Can I anyone help me out to solve this need? How would you do it? Is this the best way? I would need a smart and fast way idea. Thank you a lot! BR, Charlie1992 Read More
