Function Problems

How to Count Product Rows by Manufacturer in Excel

Algirdas Jasaitis Sep 28, 2026 868 views

Question details

The user needs to count the number of product rows associated with each manufacturer in an Excel spreadsheet where manufacturer names are repeated.

How to Count Product Rows by Manufacturer in Excel

Product: Microsoft Excel 2019
Device & OS: not provided
Scenario: Summarizing inventory or product data by grouping and counting items per manufacturer.
Observed behavior: Manufacturer names appear multiple times in column A, requiring a method to extract unique names and count their total occurrences.

Before you start

Ensure your manufacturer data in column A does not contain trailing spaces or inconsistent spellings, as these can cause inaccurate counts in formulas or split groups in a PivotTable.

Solution 1Recommended

Use the COUNTIF Function to Count Rows

Extract a unique list of manufacturers and use the COUNTIF function to count how many times each name appears in the original column.

The COUNTIF function is perfect for counting cells that meet a single specific criterion. By creating a deduplicated list first, you can cleanly summarize the entire dataset.

Extract a unique list

Copy the entire list of manufacturers from Column A and paste it into a new column, for example, Column F. Keep the pasted data selected, navigate to the 'Data' tab on the ribbon, and click 'Remove Duplicates'. Click 'OK' to leave only unique manufacturer names.

Enter the COUNTIF formula

Click the empty cell next to your first unique manufacturer (e.g., G2). Type the formula =COUNTIF($A:$A, F2) and press Enter.

Apply to all manufacturers

Select cell G2 again. Click and hold the small square at the bottom-right corner of the cell (the fill handle), and drag it down to the end of your unique manufacturer list to apply the count to all items.

Absolute References: Using absolute column references like $A:$A ensures the formula always checks the exact same range, preventing errors if you move or copy the formula elsewhere.

Solution 2

Create a PivotTable for Quick Summarization

Use a PivotTable to automatically group manufacturers and count their products without writing any formulas or manually removing duplicates.

Efficient Data Analysis

Count and Summarize Data Easily with WPS Office

WPS Spreadsheet offers powerful data analysis tools identical to Microsoft Excel. You can quickly use functions like COUNTIF or build PivotTables to count product rows by manufacturer effortlessly, completely for free.

1. Open your Dataset: Launch WPS Spreadsheet and open your product inventory file.
2. Insert a PivotTable: Highlight your data, navigate to the Insert tab, and select PivotTable to create a new summary sheet.
3. Configure the Fields: Drag the 'Manufacturer' field to the Rows section, and drag it again to the Values section to instantly generate the row count.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) file formats.Built-in advanced PivotTable features for instant, formula-free data summarization.Supports standard Excel formulas including COUNTIF, COUNTIFS, and SUMIF.Free, lightweight, and highly responsive even when handling large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my COUNTIF formula returning 0 for some manufacturers?

This usually happens due to formatting issues or hidden trailing spaces in the text. Use the TRIM function to remove extra spaces in your raw data, or double-check for minor spelling typos between your unique list and the original column.

Can I count rows based on multiple criteria, such as manufacturer and product category?

Yes, you can use the COUNTIFS function instead of COUNTIF. For example, the formula =COUNTIFS(A:A, "ManufacturerName", B:B, "CategoryName") allows you to count rows that simultaneously meet both conditions.

How do I update my PivotTable counts if I add new product rows?

If you add new data to your source table, the PivotTable does not update instantly. Simply right-click anywhere inside your PivotTable and select 'Refresh'. The counts for each manufacturer will update automatically to reflect the newly added rows.

Excel & Data Problems