Go Back

Sum If Begins With

While working with Excel, we are able to sum values in a data set based on a given criteria by using the SUMIF function.  SUMIF sums the values in a specified range based on a given condition.  This step by step tutorial will assist all levels of Excel users in summing values that begin with a specific text.  

Figure 1. Final result of the SUMIF function

Final Formula: =SUMIF(B3:B8,"Paper"&"*",C3:C8)

Syntax of the SUMIF Function

SUMIF sums the values in a specified range, based on one given criteria

Syntax

=SUMIF(range,criteria, [sum_range])

Where

  • Range: the data range that will be evaluated using the criteria
  • Criteria: the criteria or condition that determines which cells will be added
  • Sum_range: the cells that will be added; if left blank, “sum_range” = “range” which means that the range of data that will be added is the same range of data evaluated

Setting up the Data

Our table has two columns: Supplies (column B) and Quantity (column C).  We want to get the total quantity for Paper supplies and record it in cell E3.

Figure 2. Sample data for the SUMIF example

Sum Quantity if Supplies Begin with Paper

We want to sum all values in column C (Quantity) if the cells in column B (Supplies) begin with “Paper”.  Here are the steps:

Step 1. Select cell E3

Step 2. Enter the formula: =SUMIF(B3:B8,"Paper"&"*",C3:C8)

Step 3. Press Enter

The range for the data we want to evaluate is B3:B8.  The criteria is “Paper”&”*” which translates to “ Paper* ”.  The asterisk *” means that there could be any number of characters after the word “Paper”.  The sum_range is C3:C8 which is the column for “Quantity” that we want to add.  As a result, the SUMIF function returns 250, which is the sum of the quantity of “Paper_Bond” and “Paper_Art”.  

Figure 3. Using the SUMIF function to sum values of supplies that begin with “Paper”

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

IN cell I4, write a formula that will produce a TRUE if the student earned more that $15,000, last name begins with A or B, and scored above a 80 on all three tests
Solved by M. Q. in 16 mins
i need to input a part number in an excel column that I created and the number begins with a 0. Excel automatically erases it.
Solved by S. F. in 18 mins
Trying to use an =If(Left function to say that if the value in column A begins with
Solved by O. J. in 18 mins

Leave a Comment

avatar