Calculatiing the Sum of only cells prefixed by a letter

Blueorangez

New Member
Joined
Dec 15, 2016
Messages
13
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
Joined
Apr 26, 2013
Messages
6
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?
 

Spyros13

Board Regular
Joined
Mar 12, 2014
Messages
142
Office Version
  1. 2016
Platform
  1. Windows
=Numbervalue(right(A1,len(a1)-1))

then do your sumif
 
Last edited:

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,747

ADVERTISEMENT

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
Joined
Dec 15, 2016
Messages
13

ADVERTISEMENT

Would you mind explaining the formula

What are the values at the end ,2,99
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,747
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
Joined
Dec 15, 2016
Messages
13
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
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top