Pivot table grouping and return OK if condition using countif()>0

Copper Contributor

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

1 Reply

@Charles1992 

You are on the right track with your approach, but the issue arises because Excel's pivot table calculated fields do not support the COUNTIF function directly. However, you can achieve your desired result by using a combination of calculated fields and a helper column in your source data.

Here is a step-by-step approach to achieve what you need:

1. Add a Helper Column: In your source data, add a helper column that calculates whether each "Control" has any "Not Started" or "In Progress" statuses. You can use the following formula in the helper column (assuming your "Status" column is column B and "Control" column is column A):

=IF(OR(B2="Not Started", B2="In Progress"), "NOK", "OK")

Drag this formula down for all rows in your data. This will mark each "Control" with "NOK" if it has any "Not Started" or "In Progress" statuses, otherwise "OK".

2. Build Pivot Table: Create a pivot table using your data range.

3. Add Fields to Pivot Table:

    • Add the "Control" field to the Rows area.
    • Add the "Helper Column" field to the Values area. Ensure that it's set to count the occurrences (it will count "OK" and "NOK" occurrences for each "Control").

4. Add Calculated Field: Now, create a calculated field for the pivot table to check if there are any "NOK" values for each "Control".

    • Go to PivotTable Analyze > Fields, Items & Sets > Calculated Field.
    • Enter a name for the calculated field, e.g., "Status Check".
    • Enter the formula:

=IF(SUM('Helper Column')>0, "NOK", "OK")

This formula checks if the total count of "NOK" values for each "Control" is greater than 0. If so, it returns "NOK"; otherwise, it returns "OK".

  1. Click OK to create the calculated field.

Now, your pivot table should display "OK" or "NOK" for each "Control" based on the presence of "Not Started" or "In Progress" statuses.

My knowledge of this topic is limited, but since no one has answered it, I entered your question in various AI. The text and the steps was created with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

 

Was the answer useful? Mark as best response and like it!

This will help all forum participants.