can someone help me out with an array formula again?

range e4:e56 will contain a number most of the time
this number can be appended with an x. over in column g there will be an
amount. the x will always be the last character
1234 1000
5678x 500
135 200

i need a formula to sum column g4:g56 based on each condition, each in a
separate cell.
one result = 1200. the other = 500

can somebody help?


--


Gary

Here it is with a sumproduct formula. A little easier than an array formula
(in my opinion)

=SUMPRODUCT((RIGHT(TRIM(E4:E56), 1)="x")*G4:G56)

and

=SUMPRODUCT((RIGHT(TRIM(E4:E56), 1)<"x")*G4:G56)

You don't need to use Ctrl + Alt + Enter to use these formulas.

--
HTH...

Jim Thomlinson


"Gary Keramidas" wrote:

can someone help me out with an array formula again?

range e4:e56 will contain a number most of the time
this number can be appended with an x. over in column g there will be an
amount. the x will always be the last character
1234 1000
5678x 500
135 200

i need a formula to sum column g4:g56 based on each condition, each in a
separate cell.
one result = 1200. the other = 500

can somebody help?


--


Gary

worked perfectly, jim.

thanks

--


Gary


"Jim Thomlinson" wrote in message
...
Here it is with a sumproduct formula. A little easier than an array
formula
(in my opinion)

=SUMPRODUCT((RIGHT(TRIM(E4:E56), 1)="x")*G4:G56)

and

=SUMPRODUCT((RIGHT(TRIM(E4:E56), 1)<"x")*G4:G56)

You don't need to use Ctrl + Alt + Enter to use these formulas.

--
HTH...

Jim Thomlinson


"Gary Keramidas" wrote:

can someone help me out with an array formula again?

range e4:e56 will contain a number most of the time
this number can be appended with an x. over in column g there will be an
amount. the x will always be the last character
1234 1000
5678x 500
135 200

i need a formula to sum column g4:g56 based on each condition, each in a
separate cell.
one result = 1200. the other = 500

can somebody help?


--


Gary

By assuming the last character either "x" or number, the arraf formula
is:

Condition1 ("x" as the last character):

=SUM(IF(RIGHT($E$4:$E$56,1)="x",$G$4:$G$56))

Condition1 (there is no "x" at the end):

=SUM(IF(RIGHT($E$4:$E$56,1)<"x",$G$4:$G$56))

Don't forget to press CTRL + SHIFT + ENTER after editing the formula.

thanks

--


Gary


"broogle" wrote in message
ups.com...
By assuming the last character either "x" or number, the arraf formula
is:

Condition1 ("x" as the last character):

=SUM(IF(RIGHT($E$4:$E$56,1)="x",$G$4:$G$56))

Condition1 (there is no "x" at the end):

=SUM(IF(RIGHT($E$4:$E$56,1)<"x",$G$4:$G$56))

Don't forget to press CTRL + SHIFT + ENTER after editing the formula.