Function Problems

How to Count Documents with Bought and Sold Transactions in Excel

Bushra Parveen Sep 28, 2026 869 views

Question details

Identify and count documents containing both 'Bought' and 'Sold' transactions, with an option to filter documents requiring exactly one of each transaction type.

How to Count Documents with Bought and Sold Transactions in Excel

Product: Excel
Device & OS: not provided
Scenario: Grouping and analyzing financial or inventory transaction data based on document numbers and transaction types.
Observed behavior: A method is needed to group data by document number, calculate separate counts for Bought and Sold, and filter results based on specific transaction matching conditions.

Before you start

Ensure your dataset is organized in a tabular format with clear column headers, specifically a 'Document_Num' column and a 'Transaction_Type' column.

Solution 1Recommended

Use Power Query to Group and Filter Transactions

Best for filtering documents with exactly one 'Bought' and one 'Sold' transaction using Power Query grouping and calculations.

Power Query is an excellent tool for reshaping transaction data. By grouping rows by the document number, you can easily calculate both the row count and the net transaction values.

Load data into Power Query

Select your data range, navigate to the 'Data' tab, and click 'From Table/Range' to open the Power Query Editor.

Group by Document Number

Select the 'Document_Num' column, go to the 'Transform' tab, and click 'Group By'.

Set up aggregations

Create two new columns during the grouping: 'RowCount' (using the Count Rows operation) and 'QuantitySum' (using the Sum operation on your quantity column).

Filter the results

Filter the 'RowCount' column to equal 2, and the 'QuantitySum' column to equal 0 to find documents with exactly two offsetting transactions.

Load the data

Click 'Close & Load' on the Home tab to return the filtered list of documents to your Excel worksheet.

Use Power Query to Group and Filter Transactions

Assumption: This method assumes that 'Bought' and 'Sold' quantities are recorded as positive and negative values that sum to zero.

Solution 2

Use COUNTIFS Formulas to Calculate Separate Counts

A formula-based approach to count Bought and Sold rows per document without using Power Query.

Solution 3

Use SQL Queries for Database Environments

Ideal if your Excel data is connected to an external SQL database or you are using Microsoft Query to pull data.

Easily Manage Financial Data with WPS Office

WPS Spreadsheet offers powerful data grouping, Pivot Tables, and advanced filtering capabilities to help you analyze bought and sold transactions effortlessly.

1. Open Your Data: Launch WPS Spreadsheet and open your transaction dataset.
2. Insert a Pivot Table: Go to the Insert tab and click 'PivotTable' to group your data by 'Document_Num'.
3. Filter Transaction Types: Drag 'Transaction_Type' to Columns and Values to instantly see the counts of Bought and Sold documents.

Fully compatible with Microsoft Excel (.xlsx) formats and formulas.Intuitive Pivot Table tools for instant data grouping.Lightweight and runs smoothly on all operating systems.Free alternative with a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

How can I count documents with both Bought and Sold transactions using a Pivot Table?

Insert a Pivot Table, place 'Document_Num' in the Rows area, and 'Transaction_Type' in the Columns and Values areas. You can then apply a Value Filter to the rows to show only documents that have counts greater than 0 for both transaction types.

What if I only want documents with exactly one Bought and one Sold transaction?

Using the COUNTIFS method, verify that the result for 'Bought' equals exactly 1, the result for 'Sold' equals exactly 1, and ensure the total row count for that specific document number is exactly 2 to prevent additional unwanted transactions.

Why is Power Query recommended for this transaction filtering task?

Power Query simplifies data manipulation by allowing you to group, sum, and count rows in a few intuitive clicks without writing complex nested formulas. It also makes your workflow repeatable, as it can automatically refresh when new transaction data is added.

Excel & Data Problems