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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

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
140
=Numbervalue(right(A1,len(a1)-1))

then do your sumif
 
Last edited:

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,631

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,631
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,672
Messages
5,597,488
Members
414,146
Latest member
marginmakerb

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