Function Problems

How to Count HRC and ARC Values in Excel and Create a Chart

Olivia Miller Sep 27, 2026 869 views

Question details

The user needs to count specific text entries (HRC and ARC) per team and display the results visually in a chart.

How to Count HRC and ARC Values in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking and visualizing team performance or entries based on specific text codes like HRC and ARC.
Observed behavior: Data needs to be aggregated using a counting formula before it can be used as a data source for chart generation.

Before you start

Ensure your data range is properly defined and that headers for 'HRC' and 'ARC' are set up in a summary table before applying the counting formula.

Solution 1Recommended

Use COUNTIF with Wildcards to Tally HRC and ARC

Use the COUNTIF function to dynamically count text entries based on your summary table headers, then use the results to insert a chart.

The COUNTIF function allows you to count cells that meet a single condition. By combining it with a wildcard (*), you can count cells ending with or containing your target text, ensuring variations in data entry are still counted accurately.

Set up the summary table

Create headers for your counts. For example, type 'HRC' in cell J1 and 'ARC' in cell K1.

Enter the COUNTIF formula

Click on cell J2 and enter the formula =COUNTIF($E$2:$E$100,"*"&J1). Adjust the range $E$2:$E$100 to match your actual data column containing the team entries.

Fill the formula across

Drag the fill handle from the bottom-right corner of cell J2 across to cell K2 to automatically count the 'ARC' values based on the header in K1.

Insert a chart

Select your newly created summary table (e.g., J1:K2), navigate to the 'Insert' tab on the ribbon, and choose your preferred chart type (like a Column or Pie chart) to visualize the data.

Use COUNTIF with Wildcards to Tally HRC and ARC

Formula Tip: Using absolute references (like $E$2:$E$100) ensures your data range doesn't shift when you copy the formula to other cells.

Process Data Easily with WPS Spreadsheet

Count and Visualize Data Effortlessly with WPS Office

WPS Spreadsheet provides robust data analysis tools, including full support for advanced functions like COUNTIF and a wide variety of customizable charts. It's fully compatible with Microsoft Excel files, allowing you to track team metrics seamlessly.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your team entries.
2. Apply the COUNTIF function: Use the formula =COUNTIF($E$2:$E$100,"*"&J1) in your summary table to count the HRC and ARC values.
3. Create a visual chart: Highlight the summary data, go to the 'Insert' tab, and click 'Chart' to easily generate a visual report.

Fully compatible with Microsoft Excel (.xlsx) formats and formulas.Includes a built-in Chart Wizard for one-click data visualization.Lightweight software that loads large datasets quickly.Free to use for daily spreadsheet tasks and data counting.

Frequently Asked Questions

Why is my COUNTIF formula returning 0 for HRC entries?

This usually happens if the data range doesn't match the actual location of your text, or if there are trailing spaces in your cells. Try using wildcards on both sides like "*"&J1&"*" to catch the text anywhere in the cell.

How do I count HRC values for a specific team only?

To count based on multiple criteria (e.g., 'Team A' and 'HRC'), use the COUNTIFS function instead. For example: =COUNTIFS($A$2:$A$100, "Team A", $E$2:$E$100, "*HRC*").

Can I automatically update the chart when new data is added?

Yes. Format your source data as a Table (Insert > Table). This makes your ranges dynamic, so any new HRC or ARC entries added to the table will automatically update your COUNTIF formulas and the connected chart.

Excel & Data Problems