# Total of rows with a word in it

#### JoshuaMars

##### New Member
Hi there Excel masters,

Im sure this would be pretty easy for most of you, but for an novice user like myself, I cant get my head around it.

Is there a way of adding cells up a column only on rows that have a certin word in the row.

In the worksheet called 2006
A1 = January B1 = 5
A2 = February B2 = 5
A3 = Total B3 = 10
A4 = March B4 = 5
A5 = April B5 = 5
A6 = Total B6 = 10
etc
etc
etc

In worksheet called Grand Total
I would like to have B12 = the total sums of all rows in the 2006 sheet that have the word total in them.

Im sure there is sum way to do this.

Joshua

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

##### Well-known Member
Hello,

Is this working as expected?

=SUMIF(A1:A14,"=*Total*",B1:B14)

#### Richard Schollar

##### MrExcel MVP
Try:

Code:
``=SUMIF('2006'!A1:A100,"*" & A12 & "*",'2006'!B1:B100)``

Where A12 holds the string you want to search for eg "Total" (it isn't case sensitive). Adjust ranges to suit.

Best regards

Richard

#### JoshuaMars

##### New Member

As I said previous Im still only a newbie at excel, but im trying to learn.

Let em see if I have this formula correct.

=SUMIF(A1:A14,"=*Total*",B1:B14)

this basicly means do a sum over everything in column B but only of the rows that have te word Total in it.

correct. If not, that is what Im trying to do.

Joshua

##### Well-known Member
Hello,

Correct, but the formula is currently only working in rows 1-14, you will need to change the cell refs as required. If you want the whole column use

=SUMIF(A:A,"=*Total*",B:B)

#### JoshuaMars

##### New Member
Works Perfect.

thanks again heaps

Replies
14
Views
157
Replies
10
Views
123
Replies
17
Views
220
Replies
7
Views
111
Replies
11
Views
101