# Calculatiing the Sum of only cells prefixed by a letter

#### Blueorangez

##### New Member
Calculatiing the Sum of only cells prefixed by a letter.

0500 1300 0500 1330 H8.5 0500 1330 0500 1300 0500 1330 _______

I have and Excel Staff Roster of which the rows look like this.

I trying to find a Formula______ at the end of the row to retrive the sum of all the cells that have a H in the cell

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### BellaxPalus

##### New Member
Are you trying to Count the number of cells that have an H in them or are you trying to total the numbers that follow the H?

#### Blueorangez

##### New Member
Im trying to total the numbers that follow the H

#### Spyros13

##### Board Regular
=Numbervalue(right(A1,len(a1)-1))

Last edited:

#### Tetra201

##### MrExcel MVP

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=SUM(--IF(LEFT(A1:K1)="H",MID(A1:K1,2,99)))

Last edited:

#### Blueorangez

##### New Member
Perfect

That worked first time

Thank you so much

#### Blueorangez

##### New Member

Would you mind explaining the formula

What are the values at the end ,2,99

#### Tetra201

##### MrExcel MVP
Would you mind explaining the formula

What are the values at the end ,2,99
The IF(LEFT(A1:K1)="H" compares the leftmost symbol of the text string from each cell in range A1:K1 with the letter "H"
If the result of this comparison is TRUE, the MID(...) function is invoked ind its result is returned. Otherwise, the IF(...) returns FALSE.
The 2 and 99 are arguments of the MID(...) function. They say: return up to 99 symbols of the string starting from position 2.
In our case, MID("H8.5",2,99) returns text string "8.5".
The '--' converts the text string "8.5" into number 8.5, and all FALSE's into 0's.
Finally, the SUM function add together elements of the resulting array {0,0,0,0,8.5,0,0,0,0,0,0}.

Last edited:

#### Blueorangez

##### New Member
Hi Guys, I have this formula working in a column however I now wish to add an extra layer!!

I want the same effect as a "sumifs" whereby i only want the sum in the current formula to count cells that match "TRC" in the cell next door.

Many thanks

Replies
5
Views
380
Replies
2
Views
429
Replies
1
Views
331
Replies
6
Views
183
Replies
19
Views
1K

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,163,787
Messages
5,833,696
Members
430,225
Latest member
RKB_Excel

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