# 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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### 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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,629
Messages
5,838,454
Members
430,549
Latest member
jayjay2022

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back