Sum Excel Rows Based on Text

Most of the time, if you're summing cells in Excel, a simple SUM formula will do the job. In the example shown below, the SUM function calculates the total of hours worked each week.

The formula in cell B7 is:  =SUM(B2:B5)

However, things aren't always so simple. You've probably seen worksheets like the one below, in which different kinds of values are mixed together. Instead of just employee hours, the worksheet also calculates the weekly cost per employee.

Using the SUM Function

A common solution is to use the SUM function, and carefully select all the rows with hours.

That solution might work in a short list, but it's an accident waiting to happen if you try to total a long list with this technique.

And even in a short list, you'll end up with errors if an employee's data is deleted from the list.

Use the SUMIF Function

A better solution in this case is to use the SUMIF function. It sums the numbers in rows that meet a specific criterion.

=SUMIF(range to check , criteria, range to sum)

• Row headings are in column A, so that is the range the SUMIF formula will check.
• The total headings in A11 (Hours) or A12 (Cost) will be used to create the criteria.
• With a SUMIF formula in cell C11, we can sum the hours in column C.

Note: If you have multiple criteria to check, use the SUMIFS function in Excel 2007 and later versions.

Wildcard in Criterion

The row headings won't exactly match the "Hours" criteria, because the headings start with the employee name, such as "Emp01 Hours".

However, the headings are consistent, and the endings match the total headings in A11 (Hours) or A12 (Cost).

• In the rows with hours, the heading ends with "Hours"
• In the rows with costs, the heading ends with "Cost"

The wildcard character – * – represents any characters, so the criterion "*Hours" would match any heading that ends with "Hours".

To total all the Hours rows in week 1, we could use this formula:

=SUMIF(\$A\$2:\$A\$9 ,"*Hours" , C\$2:C\$9)

To make the formula more flexible, so it can be used in the Cost row too, we'll use a reference to the heading cell -- \$A11 -- instead of hard-coding the "Hours" text.

=SUMIF(\$A\$2:\$A\$9 ,"*" & \$A11, C\$2:C\$9)

Copy the Formula to Remaining Total Cells

To finish the worksheet, the formula is copied across to column F, and copied down to row 12, where it calculates the total cost.

This formula has different types of references, so it can be safely copied down to the Cost total in row 12, and across to column F.

=SUMIF(\$A\$2:\$A\$9,"*" & \$A11, C\$2:C\$9)

• \$A\$2:\$A\$9 – Absolute reference to the row headings in cells A2:A9. No matter where the formula is used, it should always check these cells for the criteria text.
• \$A11 – The column reference is absolute, so it always uses the criteria in column A of the total row. The row reference is relative, so it will change when the formula is copied down to row 12.
• C\$2:C\$9—The column reference is relative, and will change as the formula is copied across to columns D, E and F. The rows are an absolute reference, and will continue to sum rows 2:9 when the formula is copied down to row 12

More SUMIF Examples

For more SUMIF and SUMIFS examples, please visit the Excel SUM functions page on the Contextures website.

___________

29 Responses

1. Kurt says:

>> To make the formula more flexible, so it can be used in the Cost row too, we’ll use a reference to the heading cell — \$A11 — instead of hard-coding the “Hours” text.

>> =SUMIF(\$A\$2:\$A\$9 ,”*” & \$A11, C\$2:C\$9)

shouldnt the efrence goto \$A13 ??

• @Kurt, thanks, you’re right — I had the wrong screen shot in one section. It’s fixed now.

2. Martin says:

Wow, I would have added another column named Type, and used the same approach, just thinking that the next request could be for someone to ask for a Pivot Table….

• @Martin, if they’re going to ask for a pivot table, we’ll have to start from scratch, and set this table up properly. ;-)

3. Andy says:

This is ‘Excel’lent!!! It can be so annoying when you need to delete listed data.

4. Fiasco says:

Hi. Tnx for the tutorial, but i ran into a problem.
What would you do, if you needed to sum Emp05 Costs only for odd number weeks(week 1, week 3, week 5 etc.)?

5. Mahendra Rathor says:

Great help to what I was trying to do.

Many thanks

Best regards

6. Farlwy says:

How can I Sum only the latest 5 values in a row of many values that may have no value cells interspersed? The cells would be laid out in a time sequence.

7. Holly says:

Thanks for this quick help! I forgot an absolute value and was so frustrated because my formula was not carrying across the sheet. This was very easy to follow and translate to my usage.

8. J-Ray says:

Thank you thank you thank you! Excellent example of how this function works. It took me a while to find the perfect function.

9. beks says:

Hello

I have a question and need help please.

Are you able to add a value to a box and have the sum/formula in that box which will times that number by a specified amount and give you the total?

i.e. I want to times £23.00 by the number i add to the box, which will differ each month.

not sure if this is even possible by any help gratefully received!

thanks
Beks

10. Belks says:

You would nee to use 2 cells, let’s say cell A1 would contain the number that you would add and cell B1
would contain the formula. This formula in B1 will work =IF((A1=””),””,A1*23) cell B1 will show nothing until a value has been put in cell A1

11. Guido van Es says:

THANK YOOOOUUUUUUUU This helped a great deal!!! :-)

12. Leslie Charles says:

You can also use a formula like =SUMIF(\$B\$5:\$B\$42,B45,\$D\$5:\$D\$42)

Suppose you have a long list with different categories. For example Rent, Utilities,
Supplies, etc. There may be many different instances of each category (e.g., Jan Rent = \$50, Feb Rent = \$50 Jan Supplies = \$500, Feb Supplies = \$200 etc.). So, at the bottom of the long list you enter the categories then place the formula in an ajacent column and copy-down. If the sum of each category equals the sum of the long list then you are sure your spreadsheet is accurate.

13. Simon says:

Thank you very much for this great explanation – I never write comments, but this helped me in a clear and concise way that was easy to implement. Cheers!

14. mark says:

I am trying to sum four cells in a row for a completion report. The problem is the four rows represent four topics safety topics. I insert a 1 in each cell showing complete. To the right of the four cells is a cell showing completion %. I used a simple =sum formula/4 that works OK. Now I need to modify this to show a number reflecting date of hire. This would now reflect four columns with a unique number excusing the employee from completing the assigned reading and also showing completion % for dates other than start date. Sorry its so confusing. In other words, three of the four cells are part of a % formula excusing one cell as the start date?

15. Joe says:

I have a spreadsheet of email addresses and want to count the number of times an email address appears in a row. Please assist, and many thanks.

Joe

16. Dave Roberts says:

Debra has a whole list of countif tutorials. One of these should help.
http://www.contextures.com/xlFunctions04.html

17. chiru says:

how to calculate an employee one year total expenses from every month expenses file

18. Craig says:

I am trying to ADD a value from the first cell in a column cell “A2” to each cell in a row of cells starting at row B1 thru AJ1 and place the added values in row cells B2 thru AJ2.

Then ADD the value in column cell A3 to each cell in row B1 thru AJ1 and place the added values in row cells B3 thru AJ3.

Next, ADD the value in column cell A4 to each cell in row B1 thru AJ1 and place the added values in row cells B4 thru AJ4…etc, for several values in the column cells.

I would like to be able to drag this ADD operation through the cells in each row.

I cannot find any function in Excel that will allow this operation.

What do you suggest?

19. Gary Williams says:

I have an attendance register where attendance is recorded as A = Unauthorosed absence, a = authorised absence, L = Late more than 15 minutes and l = late less than 15 minutes

I would like to add up the ‘A’, ‘a’ and ‘L’ but ignore ‘l’ and blanks

Any advice grateatly appreciated

20. Lorri says:

I completed the 1st half of my project the hard way (took 2.5 hrs), then I found this tip and it only took me 15 mins to complete the 2nd half of the project! Thank you so much for this information! Thank you so much for posting Debra!

• @Lorri, you’re welcome! Thanks for letting me know that it helped you.

21. cris says:

how to automatic sum in excel while format is text it is possible?

22. Sarah says:

This week I have a project that I really needed to utilize the SUMIFS formula for (to sum values based on two other conditions, both of which were text qualifiers and were on a different sheet in the same workbook), and I ran into some trouble as Excel was not treating the text as part of the formula. I found a workaround: make sure you have formatted the cell (that your formula is in) to be a number or currency or something other than text BEFORE you enter your formula. Then when you are selecting your ranges, go to that sheet and select them by hand rather than typing in the sheet and the cell range. Not sure why this made all the difference as my formula was otherwise exactly the same, but I’m thrilled it worked. I never had this issue with Excel 2003.

23. Markus says:

I’m having an issue with SUMIFS.
I have Column A filled with names: Bob, Joe, Mary, etc.
I have Column B filled with departments: Marketing, Sales, etc.
I have Column C filled with numbers.
In Sheet2, however, I have a list of people missing for whatever reason: sick, pregnant, vacation, etc. I add or subtract names as they become available/unavailable.
What I’m trying to do is have SUMIFS total up Column C for everyone that is currently available by checking the name in Column A against my unavailable list in Sheet2, Column A.

=SUMIFS(C:C,A:A,”Sheet2!A:A”,B:B,B3)

It’s correctly counting everyone from Sales (identified in Cell B3) including names that appear on Sheet2.

• Markus says:

Grr… it won’t let me edit.
The formula I’m trying to use:
=SUMIFS(C:C,A:A,”Sheet2!A:A”,B:B,B3)