Hey guys, i have been messing with this for a while and cant get it to work right. I have a totals table on my sheet and it sums up all the worked hours in a pay period. normally this wouldnt be an issue but my boss would like it contain abreviations as well. So my input cells will contain a number and a 3 digit code.
Example: 8 HOL
would be 8 hours paid for a Holiday
This is my original sum code
=SUM(D5,Q5,D23,Q23,D41,Q41,D59,Q59,D77,Q77,D95,Q95,D113,Q113)
Simple and to the point
How can adapt it to comply?
Summing Numbers Of Multiple Cells That Also Contain Text
-
-
-
Re: Summing Numbers Of Multiple Cells That Also Contain Text
Can you use formatting to add the HOL to the cells so that it actually only contains the numbers? In other words, use a custom number format of:
0" HOL" -
Re: Summing Numbers Of Multiple Cells That Also Contain Text
I looked through the format options as you suggested but nothing in there works. I also tried the format you provided and no go there either. Maybe i misunderstood you, could you be more spicific? Thanks rory
-
Re: Summing Numbers Of Multiple Cells That Also Contain Text
You need to apply the format to the cells you are summing - i.e. D5, D23 etc. then you can have actual numbers in them. For example, if you format D5 as 0" HOL" and type 8 in it, it will display as '8 HOL' but still work in the SUM formula.
Without knowing your workbook layout I have no idea if that will help you. -
Re: Summing Numbers Of Multiple Cells That Also Contain Text
[ATTACH=CONFIG]35628[/ATTACH] Here is an example rory
-
-
Re: Summing Numbers Of Multiple Cells That Also Contain Text
@ rory
I see how you meant it know. However the 3 digit code might not be entered at all. And the code can change. I have around 5 codes that could be used at any time. See my delema? I really appreciate all your help. -
Re: Summing Numbers Of Multiple Cells That Also Contain Text
Just a thought for you, but can you add a column for your code? That way the text won't affect your sum formula.
-
Re: Summing Numbers Of Multiple Cells That Also Contain Text
I could but i would have to redesign the whole thing lol
-
Re: Summing Numbers Of Multiple Cells That Also Contain Text
Use another worksheet that links to the original source sheet and formula that extracts the number portion of the string
On Sheet2
D5 cell contains: =LEFT(Sheet1!D5,1)+0
etc.Then, sum the applicable cells on sheet2.
Will that work for you?
If your cells weren't in a non-contiguous range, an array formula would be possible.
-
Re: Summing Numbers Of Multiple Cells That Also Contain Text
I see what you mean. Yes that would work. I will try it. Thanks alot
-
-
Re: Summing Numbers Of Multiple Cells That Also Contain Text
Based on your original formula, which appears to involve regularly spaced cells and assuming a space between the numbers and the letters:
=SUMPRODUCT((MOD(ROW($D$5:$Q$23)-5,18)=0)*(MOD(COLUMN($D$5:$Q$23)-4,13)=0)*IF(ISNUMBER(SEARCH(" ",$D$5:$Q$23)),--LEFT($D$5:$Q$23,SEARCH(" ",$D$5:$Q$23)-1),IF(ISNUMBER($D$5:$Q$23),$D$5:$Q$23,0)))
array-entered.An additional column/sheet would indeed be easier and more efficient though!
-
Re: Summing Numbers Of Multiple Cells That Also Contain Text
Nicely done rory thats done it.
Everyone else thanks for your help; -
Re: Summing Numbers Of Multiple Cells That Also Contain Text
Of course, you need to expand the range to suit - I only tested with D5:Q23.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!