How to Sum Names in Excel (4 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

Anyone wishes to sum for specified names in Excel for business, academics, or other purposes. We will try our best to show some ways to sum names in Excel that can be human or business entities or any kind of name. We have discussed a number of straightforward methods in this post to sum names in Excel. So, stay with us and adhere to the process.

A dataset from the production of textiles is shown in the picture below. In the following sections, we will discuss quick methods to know the overall price or total quantity for certain customer names in Excel.

Sample Dataset to show how to Sum Names in Excel


1. Sum for One or Multiple Names Using Excel SUMIF or SUMIFS Function

SUMIF or SUMIFS functions allow us to sum for specific criteria. In the first method, we will use these functions to sum data for one or more names.

📌 Steps:

  • To know the total quantity and total order price of customer-1, put the following formulas in cell J5 and K5.

Formula for Total Quantity:

=SUMIF(C5:C20, C5, F5:F20)

Formula for Order Price:

=SUMIF(C5:C20, C5, G5:G20)
  • In both cases, press ENTER and you will see that we’ve got specific order data for Customer-1.

SUMIF or SUMIFS functions to sum data for one or more names

  • In a similar manner, we can apply the SUMIFS function to add more criteria. For example, if you want to know the total order quantity and price for Customers only for those greater than 100 pieces, then you have to insert the following formula in cells J12 and K12.

Formula for Total Quantity (Which Are >100):

=SUMIFS(F5:F20,C5:C20,C10, F5:F20,">100")

Formula for Total Price (Whose Quantity Are >100):

=SUMIFS(G5:G20,C5:C20,C10, F5:F20,">100")

💬 Note:

You could use “Customer-1” instead of C10 inside the formulas to set name criteria.

Read More: How to Sum If Cell Contains Text in Another Cell in Excel


2. Sum Names for Partial Matches in Excel

We have products such as shirts, t-shirts, and shorts. You see “sh” is common in these products’ names. So we can set a formula using wildcard characters to get the total for all these products in a single click. Here is how to do this.

📌 Steps:

  • Go to cell J5 and insert the following formula.
=SUMIF(E5:E20, "*Sh*", F5:F20)
  • Then hit the ENTER button.

Sum Names for Partial Matches in Excel Using Wildcards

Read More: How to Assign Value to Text and Sum in Excel


3. Performing Summation of a Named Range in Excel

In the third example, we’ll use the same procedure as before, but this time using Named Range. Named Range defines a range of cells by selecting those cells and renaming them in the name box.

📌 Steps:

  • First off, we will select the product column and as shown in the image below. Rename it as Products. Use an underscore (_) if you want to use two or more words in the name box.

create Named Range

  • Similarly, rename the Quantity Column as Quantities.

  • Now apply the following formula in cell J5.
=SUMIF(Products, "*Sh*", Quantities)
  • Notice that we don’t have to use the range as a reference, rather we have directly used the name specified by us before.


4. Using Excel Consolidate Option to Summarize Total for Specified Names

To examine a list that has been condensed in Excel, we can simply combine the columns labeled “Customer”, “Qty”, and “Total Price” but this time by consolidating.

📌 Steps:

  • We may quickly and simply summarize a list of names and their matching quantities and total price volume by choosing to Consolidate under Data Tools under the Data tab.

Using Consolidate

  • A Consolidate pop-up will then show up here.
  • Select Sum in the Function area.
  • Then select the data range you want to consolidate. Here it is C5:G20.
  • Now mark the Left Column under Use Labels.
  • Click OK when finished.

A Consolidate Pop-up

  • But in this table we have consolidated, we have some undesirable columns that is not our concern here right now like the 2nd (K) and 3rd (L) column.
  • So we will select the 2nd and 3rd columns and by right-clicking, select Delete.

Right-click and Delete a Column

  • So here is the final data that we desire to see the total quantity and total price to the related customers.


Download Practice Workbook

You can download the practice workbook from the following download button.


Conclusion

These procedures and stages will carry out necessary tasks to sum names in Excel. The workbook is also available for download so that you may use it for your own practice.


Related Articles


<< Go Back to Excel Sum If Cell Contains Text | Excel SUMIF Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Ikram Amit
Al Ikram Amit

Al Ikram Amit, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a key Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Amit not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently delivering exceptional, high-quality content that adds significant... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo