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!

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

On 27 Sep 2006 04:26:27 -0700, 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!

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

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!

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

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!

"Ron Rosenfeld" wrote:

On 27 Sep 2006 04:26:27 -0700, 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!

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

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!

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

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

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

On 27 Sep 2006 05:26:30 -0700, wrote:

:)

=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

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

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

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

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

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

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)))

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

On 29 Sep 2006 03:15:44 -0700, wrote:

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?



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

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!