• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum cells with numbers and letters

okiearcher

New Member
Hello!

First time poster here.

I'm sure it's simple to some, but I need to total the number portion of a range of cells where the cells might have numerical (leading portion) and alphabetic (trailing portion) value.

Example: Range of cells might be B2:M32
Some cells might contain values like "8 hrs FH" or "2 hrs FH" or "24 hrs FH".
Some other cells might contain values like "16 hrs VAC" or "40 hrs VAC", etc...
(without the quotes)

I need a formula that will total up all the numerical values from the cells that end with FH, and another formula that will total the numerical values of cell that end with VAC.

I could probably manage this with VBA, but I think formulas are more suited for this application.

Any help?
Thanks.
 
Awesome!

Works great. I'll take that and apply it to my sheets (5) of them so each sheet will calculate.

Thanks for taking the time to help!
 
Wow vletm. This just flows out of your head!

With the one button approach, now I have an idea to make a 'dashboard' sheet as sheet1. One button would total FH on each Sheet, putting Sheet2 total FH in Sheet1!B5, Sheet3 total FH in Sheet1!C5, Sheet4 total FH in Sheet1!D5, etc.

Then put Sheet2 total VAC in Sheet1!B6, Sheet3 total VAC in Sheet1!C6, Sheet4 total VAC in Sheet1!D6.

If you have a quick solution, I'd love to see it. Otherwise I will start studying your code example and see if I can modify to fit my new 'need'.

Thanks very much. What a learning experience....
 
No matter of TAB's names
No matter how many TABs
1st TAB is like 'dashboard' ( no matter of name ), not 'solved'.
No need to solve all TABs.
from A5:A_ comes TABs names too.
Something like this?
Ideas?
 

Attachments

  • okiearcher.xlsm
    53.2 KB · Views: 6
Bravo. That works great.

I will figure out how to make it just do all three sheets automatically without asking if I want to move to the next sheet. It's a bit different coding style than I am used to. I typically use Option Explicit and identify data types in my variable names, but I'll pour over it and figure it out. I'm sure to learn from it.

Thank you very much for taking the time.

I've just discovered this site yesterday. I know where to come for help and answers now.

Thanks again!
 
Hi,

Just putting formula to your initial query. Considering naming your data range. Than try below array formula to get the sum.

For FH
=SUM((--ISNUMBER(SEARCH(" FH",dataRange)))*(--(IFERROR(LEFT(dataRange,FIND(" ",dataRange)-1),0))))

Replace FH by VAC to get the respective values.

Note above formula in an array formula so must be entered with Ctrl+Shift+Enter.

Regards,
 
Back
Top