# Creating a total (sum) of items matching a text value

1. ## Creating a total (sum) of items matching a text value

New to the forum and my first post. I am trying to create a total of hours in a cell based on the range of values (in this case categories) in a column (example attached). Obviously I am very rusty when it comes to Excel formulas. It would be really nice if I could figure out a way for excel to know that a new category has been added and sum those as well. Any help from you experts will be much appreciated.  Register To Reply

2. ## Re: Creating a total (sum) of items matching a text value

Try:

=SUMIF(\$B\$2:\$B\$8,RIGHT(A11,2),\$E\$2:\$E\$8)

copied down

You can convert your list in A10:A13 to Tables so that when you add a new category, the formula copies down.  Register To Reply

3. ## Re: Creating a total (sum) of items matching a text value

Thanks for the reply NBVC! That gets me part of the way there. However, it doesn't address when I have more than on row that has the same category entered. In this example, I need it to calculate the total of all the hours associated with each category. A specific example would be to add up all the category 1 hours in the top table. Thanks again.  Register To Reply

4. ## Re: Creating a total (sum) of items matching a text value

I don't understand, if in A11 you enter "Category 1" using my formula in B11, don't you get 90? Which is the sum of 30+20+40 (i.e. all the Category 1 entries).  Register To Reply

5. ## Re: Creating a total (sum) of items matching a text value

Yes indeed! My bad. It's been one of those mornings. Thank you soooo much!  Register To Reply