PivotTables are one of the most powerful tools in Microsoft Excel, but sometimes, based on your dataset structure it can miscount repetitive or reoccurring values. This blog I’ll explain why PivotTables overcount records in your data and how to fix it using the COUNTIFS function.
Understanding the Dataset
Get Access to Your File
Enter your name and email to receive access to the file.
In this example, we have an orders dataset, where each order can have multiple shipments. The dataset includes the following columns:
- Order Number – Unique identifier for an order
- Shipment Number – A shipment linked to an order (can repeat)
- Item Code – Unique code assigned to each product
- Item Text – The name of the product
- Shipment Qty – The quantity shipped for that item
- Count Shipment – This is helper column which will count the shipment
Issue in PivotTable
When we create a PivotTable to count shipments per order, Excel incorrectly counts all rows instead of unique shipment numbers. The issue isn’t with Excel but with the way data is structured. When working with repetitive values in datasets like orders and shipments, PivotTables count all occurrences instead of unique records.
For example:
- Order 932500 has only 2 unique shipment numbers (7643 and 7924),
- But the PivotTable counts 6 shipments (because each row is counted separately).
To correctly count unique shipment numbers per order, we use the combination of IF & COUNTIFS formula in the Count Shipment Column.
= COUNTIFS ( $B$2 : B2 , $B2) COUNTIFS($B$2:B2, $B2) counts the number of times the same Shipment Number has appeared within the specified cell range, using an incremental reference to track its occurrences.
=IF ( COUNTIFS ( $B$2:B2, $B2) = 1 , 1 , 0 ) The IF function then checks each row if the count equals 1 and if true, it assigns 1 (marking it as a unique shipment). Otherwise, it returns 0, avoiding duplicate counts.
By summing Count Shipment column in the PivotTable, we get the correct count of unique shipments per order.
Using COUNTIFS and summing the Count Shipment column, we can ensure our PivotTable correctly counts the number of unique repetitive values per order, avoiding duplicate records. This trick is especially useful for analysis record.
💡 Try this method in your dataset and let me know if it helped!
🚀 Like, Share, and Subscribe for more Excel tips!