Calculatiing the Sum of only cells prefixed by a letter

[Blueorangez]

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

 

[BellaxPalus]

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]

Im trying to total the numbers that follow the H

 

[Spyros13]

=Numbervalue(right(A1,len(a1)-1))

then do your sumif

 

[Tetra201]

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

 

[Blueorangez]

Perfect

That worked first time

Thank you so much

 

[Blueorangez]

Would you mind explaining the formula

What are the values at the end ,2,99

 

[Tetra201]

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

 

[Blueorangez]

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