LinkBack URL
About LinkBacks
Please see attached sheet. I can count the number of occurances that a Variable is found in a string but need a formula that will find that variable and sum up only the first set of numbers to the left.
Thanks
Please see attached sheet. I can count the number of occurances that a Variable is found in a string but need a formula that will find that variable and sum up only the first set of numbers to the left.
Thanks
look into attachnent array formula
Regards
tom1977
If You are satisfied with my solution click the small star icon on the left to say thanks.
Tom, thanks for the quick response, its close but not quite there. With the formula in WHI the result should be 10.5 not 2.
Sorry, checking your formula I see I have misled slightly. I need a sum of the number to the left of that Letter not from the far left.
Maybe upload new attachment with all expected results in every condition but in my opinion it will be rather hard to gain without VBA
Please find attached workbook with expected results
Thanks
Try this array formula
=SUMPRODUCT((ISNUMBER(FIND(G3,$A$4:$E$4,1)))*(IFERROR(MID($A$4:$E$4,FIND(G3,$A$4:$E$4,1)-1,1),0)))
If you liked my solution, please click on the Star -- to add to my reputation
If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.
Again it is close but not quite, the formula appears to be adding only the first number on the left ie. "1" and not the whole number ie. "10.5". I can see that the number of characters in the formula is set to 1 but I don't know how to make that a variable to suit all.
is VBA an option for this? or do you want to stick to a formula?
VBA may be an option, I have a large number of cells I need the formula in but a pre-defined Function could work ok.
From your 1st example
With 4S.4H. you only want to use the 4' I can use a different separator " . " if it helps
But
10.5WHI. you want to use the 10.5
Using a comma as a seperator would help a lot.
you could use this UDF, i changed the delimiter to ;
put this code in a new module
in your sheet use this syntax
Please Login or Register to view this content.
=FindVarSum(G3,$A$4:$E$4)
With comma separator try this formula in G4
=COUNTIF($A$4:$E$4,"*"&G3&",*")
then this "array formula" in H4
=SUM(IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT($A4:$E4,FIND(G3&",",$A4:$E4)-1),",",REPT(" ",9)),9))+0,0))
confirmed with CTRL+SHIFT+ENTER
....and then drag both of those across, see attached
Audere est facere
Thank you very much, would not of got there without you. I will go with the Function option as this is a lot easier to input.
or
in a cellPlease Login or Register to view this content.
PHP Code:=freq_snb($A$4:$E$4;G3)
=sum_snb($A$4:$E$4;G3)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
,
Bookmarks