# Sumif a row where some cells have one letter and one number and some are blank

#### MrEd777

##### New Member
I am using windows 7 with excel 2013.

I want to simplify my sheet by using sumif or some other function to add all values in cells that contain one letter and one number in row 6, column C-AM.

One cell may have V8 (for 8 vacation hours used) and some other cell may have S4 (for 4 sick hours used) and most will be blank. I then want to add up all the vacation hours, all the sick hours, and so on. The user can insert their own categories in column AS 15-19 and use that letter for the time, and that is where I want to get the criteria from.

 A B C D E F G H I J 1 S M T W T F S S M T 2 1 2 3 4 5 6 7 8 9 10 3 V8 V8 S4 S8

<tbody>
</tbody>

I want to add up row 3.

I have a snip of the sheet, but I don't know how to add it.

Does this make sense? This is my first post here, so please bear with my amateurishness.

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### pgc01

##### MrExcel MVP
Hi
Welcome to the board

See if this example helps. In M3:

=SUM(IFERROR(0+SUBSTITUTE(\$A3:\$J3,M2,""),0))

This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.

#### MrEd777

##### New Member
That worked perfectly! Thank you so much.

I have tried at least 20 different formulas and was getting nowhere.

Hi
Welcome to the board

See if this example helps. In M3:

=SUM(IFERROR(0+SUBSTITUTE(\$A3:\$J3,M2,""),0))

This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.

ABCDEFGHIJKLMNO
1SMTWTFSSMT Total
212345678910 VS
3 V8V8 S4 S8 1612
4
[book1]Sheet4

<tbody>
</tbody>

#### pgc01

##### MrExcel MVP
You're welcome. Thanks for the feedback.

Replies
6
Views
111
Replies
5
Views
92
Replies
6
Views
107
Replies
4
Views
122
Replies
7
Views
97