Forum Discussion

Charles1992's avatar
Charles1992
Copper Contributor
Apr 23, 2024

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...
excel
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Apr 24, 2024

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.

Resources