sum first 2 chars if 3rd is something...

K

krojc

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!
 
R

Ron Rosenfeld

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!
Click to expand...

Try this approach instead:

=SUMPRODUCT(LEFT(rng,FIND("-",rng)-1)*(MID(rng,FIND("-",rng)+1,1)="A"))

where your data is in "rng" (e.g. H8:K8)


--ron
 
G

Guest

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
 
G

Guest

third solution with an array formula:

=SUM(IF(MID(A1:A5;4;1)="a";VALUE((LEFT(A1:A5;2)));0))
accept this formula with "ctrl + shift + enter", as it is an array formula!
 
G

Guest

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




„[email protected]†ezt írta:
 
K

krojc

:)
=SUMPRODUCT(LEFT(rng,FIND("-",rng)-1)*(MID(rng,FIND("-",rng)+1,1)="A"))
Click to expand...

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
 
K

krojc

Damn you're fast :) I need to check others if same error does not
effect empty cells.
Thanks
 
R

Ron Rosenfeld

:)


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
Click to expand...

For the cells that are in a different format, how do you wish to process them?
--ron
 
K

krojc

For the cells that are in a different format, how do you wish to process them?
--ron
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

I guess it would be helpful if I actually posted the formula!!

=SUM(--IF(ISERR(FIND("-A",rng)),0,LEFT(rng,2)))


--ron
 
K

krojc

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?
 
R

Ron Rosenfeld

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?
Click to expand...

Glad it is working.

Recalculation depends on the setting in Tools/Options/Calculations. If you set
that to Automatic, it should recalculate with any change in the precedent data.
--ron
 
K

krojc

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!
 
Top