Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum first 2 chars if 3rd is something...
hello,
I got a row of data formated in xx-yy, where xx represents percentage (number), - is delimiter from and yx definition as letter+number. (e.g. 55-A1, 60-V2...) What I want to do is sum only first two xx values, if y equals A for the whole row. I tested the sum finction =SUM(VALUE(LEFT(a1:a10;2))), which in function dialog view sums the values correctly, while in the spreadsheet (after pressing enter), it returns a #VALUE! error. What I came up with (and should work, as I understand) is =SUMIF(LEFT(H8:K8;4);"*a*";LEFT(H8:K8;2)) =IF(H8:K8="*a*";SUM(LEFT(H8:K8;2));) but it does not work. Where do I make a mistake? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum first 2 chars if 3rd is something...
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum first 2 chars if 3rd is something...
Try this:
=SUMPRODUCT((MID(H8:K8,4,1)="A")*LEFT(H8:K8,2)) Note: that formula is not case sensitive. For an exact match, try this: =SUMPRODUCT(EXACT(MID(H8:K8,4,1),"Y")*LEFT(H8:K8,2 )) Does that help? *********** Regards, Ron XL2002, WinXP " wrote: hello, I got a row of data formated in xx-yy, where xx represents percentage (number), - is delimiter from and yx definition as letter+number. (e.g. 55-A1, 60-V2...) What I want to do is sum only first two xx values, if y equals A for the whole row. I tested the sum finction =SUM(VALUE(LEFT(a1:a10;2))), which in function dialog view sums the values correctly, while in the spreadsheet (after pressing enter), it returns a #VALUE! error. What I came up with (and should work, as I understand) is =SUMIF(LEFT(H8:K8;4);"*a*";LEFT(H8:K8;2)) =IF(H8:K8="*a*";SUM(LEFT(H8:K8;2));) but it does not work. Where do I make a mistake? Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum first 2 chars if 3rd is something...
Your function =SUM(VALUE(LEFT(a1:a10;2))) works if you have xx-yx type
values not in a row but in Column A (A1:A10) and you confirm it with Ctrl+Shift+Enter (array formula). =SUMPRODUCT(--(MID(H8:K8,4,1)="A"),VALUE(LEFT(H8:K8,2))) sums up xx values in range H8:K8 if x="A" Regards, Stefi ezt *rta: hello, I got a row of data formated in xx-yy, where xx represents percentage (number), - is delimiter from and yx definition as letter+number. (e.g. 55-A1, 60-V2...) What I want to do is sum only first two xx values, if y equals A for the whole row. I tested the sum finction =SUM(VALUE(LEFT(a1:a10;2))), which in function dialog view sums the values correctly, while in the spreadsheet (after pressing enter), it returns a #VALUE! error. What I came up with (and should work, as I understand) is =SUMIF(LEFT(H8:K8;4);"*a*";LEFT(H8:K8;2)) =IF(H8:K8="*a*";SUM(LEFT(H8:K8;2));) but it does not work. Where do I make a mistake? Thanks! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum first 2 chars if 3rd is something...
:)
=SUMPRODUCT(LEFT(rng,FIND("-",rng)-1)*(MID(rng,FIND("-",rng)+1,1)="A")) Thank you! Works as expected, but i just found out, that some cells (that I do not need) are empty or in a different format (xxxx) and those rows return #VALUE! error! Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum first 2 chars if 3rd is something...
Damn you're fast :) I need to check others if same error does not
effect empty cells. Thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum first 2 chars if 3rd is something...
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum first 2 chars if 3rd is something...
For the cells that are in a different format, how do you wish to process them? --ron Yes, I'm banging my head on this. I've got big majority of values in xx-yx format X-number, Y letter, which is already correctly processed by the formula. Some are xx-yyyyy+, which also gets through and some xx-y, the same without error. Unfortunately I've got also 0, ', and empty cells. These, especially empty cells, cause problems. It would be the best if these got totally ignored. What i did was combine the formula with COUNTIF(a1:a10;"*A*"), to count the number of these occurrences, which also includes cells that only contain this letter, but were not summed. I got much better result with "*-A*", but I found at least one cell that had -A in the wrong place, so it was also not calculated. Is there an easy way to only use xx-yx format, also count the number of those used and ignore 0 end empty? I don't seem to be writing my if clauses correctly. Thanks |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum first 2 chars if 3rd is something...
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum first 2 chars if 3rd is something...
On Thu, 28 Sep 2006 18:04:14 -0400, Ron Rosenfeld
wrote: On 28 Sep 2006 07:04:11 -0700, wrote: For the cells that are in a different format, how do you wish to process them? --ron Yes, I'm banging my head on this. I've got big majority of values in xx-yx format X-number, Y letter, which is already correctly processed by the formula. Some are xx-yyyyy+, which also gets through and some xx-y, the same without error. Unfortunately I've got also 0, ', and empty cells. These, especially empty cells, cause problems. It would be the best if these got totally ignored. What i did was combine the formula with COUNTIF(a1:a10;"*A*"), to count the number of these occurrences, which also includes cells that only contain this letter, but were not summed. I got much better result with "*-A*", but I found at least one cell that had -A in the wrong place, so it was also not calculated. Is there an easy way to only use xx-yx format, also count the number of those used and ignore 0 end empty? I don't seem to be writing my if clauses correctly. Thanks I'm still not sure what you want to do with cells in the format of: xxyy 55A1 xy-y... 5A-A123 xx-yyy... 55-ABCD or even if those formats are possible. The following *array* formula will sum the left two digits for any cells that have the format of xx-A..... and ignore others. If the left two characters are not digits, it will give an error message. To enter an *array* formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. If the above is not what you want, you will have to be specific about the types of inputs you might have and how you want to process the variations. --ron I guess it would be helpful if I actually posted the formula!! =SUM(--IF(ISERR(FIND("-A",rng)),0,LEFT(rng,2))) --ron |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum first 2 chars if 3rd is something...
Thank you Ron. :)
It works perfectly! I can replace the wrong formatted values, but funny thing is, that array formulas do not get updated untill I press "save". Is this normal? Ron Rosenfeld wrote: The following *array* formula will sum the left two digits for any cells that have the format of xx-A..... and ignore others. =SUM(--IF(ISERR(FIND("-A",rng)),0,LEFT(rng,2))) |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum first 2 chars if 3rd is something...
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum first 2 chars if 3rd is something...
Great! The job is done! :)
About the calculations. I have *never* changed this setting, but it always was set to authomatic. Ah well, If it works... Thanks again! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to bold number of chars from end of a string | Excel Discussion (Misc queries) | |||
determine if ea value in a col has more than 20 chars | Excel Discussion (Misc queries) | |||
HYPERLINK link_location could not exceed 255 chars | Excel Discussion (Misc queries) | |||
Copy sheets with more than 255 chars in a cell? | Excel Worksheet Functions | |||
geomean ignoring blank cells and chars | Excel Worksheet Functions |