Summing values
 

Hi,

I have several columns of data in the format "nnt" where n= numeric value,
t=text.

I need to sum up the numeric values, based on the text value.

Is there any way I can do this?

Thanks for the help.

You need to give us more information, is it always 2 digits and one text
character or can there be more than one text character? What would the
condition be. For a test assume there is always one text character and
always at the end

12t
1n
24t
9t
16n

assume you want to sum all the numeric values that ends with the text
character t

=SUMPRODUCT(--(RIGHT(A1:A5)="t"),--(SUBSTITUTE(A1:A5,RIGHT(A1:A5),"")))

will return 45

--
Regards,

Peo Sjoblom

(No private emails please)


"neil" wrote in message
...
Hi,

I have several columns of data in the format "nnt" where n= numeric value,
t=text.

I need to sum up the numeric values, based on the text value.

Is there any way I can do this?

Thanks for the help.

Suppose the column in question is A, then use the following formula:
=SUMPRODUCT(--(RIGHT(A1:A4,1)="a"),INT(LEFT(A1:A4,2)))

I have assumed:
A1 = 22a
A2 = 23b
A3 = 24a
A4 = 25c

Teh above formula returns 46 for t = a

Mangesh


"neil" wrote in message
...
Hi,

I have several columns of data in the format "nnt" where n= numeric value,
t=text.

I need to sum up the numeric values, based on the text value.

Is there any way I can do this?

Thanks for the help.

Thanks, works like a charm.

Can this be modified to count how many instances of the text value appears?
For instance, a countif based on "a"?

Regards

"Mangesh Yadav" wrote:

Suppose the column in question is A, then use the following formula:
=SUMPRODUCT(--(RIGHT(A1:A4,1)="a"),INT(LEFT(A1:A4,2)))

I have assumed:
A1 = 22a
A2 = 23b
A3 = 24a
A4 = 25c

Teh above formula returns 46 for t = a

Mangesh


"neil" wrote in message
...
Hi,

I have several columns of data in the format "nnt" where n= numeric value,
t=text.

I need to sum up the numeric values, based on the text value.

Is there any way I can do this?

Thanks for the help.

=SUMPRODUCT(--(RIGHT(A1:A4,1)="a"))

Mangesh


"neil" wrote in message
...
Thanks, works like a charm.

Can this be modified to count how many instances of the text value
appears?
For instance, a countif based on "a"?

Regards

"Mangesh Yadav" wrote:

Suppose the column in question is A, then use the following formula:
=SUMPRODUCT(--(RIGHT(A1:A4,1)="a"),INT(LEFT(A1:A4,2)))

I have assumed:
A1 = 22a
A2 = 23b
A3 = 24a
A4 = 25c

Teh above formula returns 46 for t = a

Mangesh


"neil" wrote in message
...
Hi,

I have several columns of data in the format "nnt" where n= numeric
value,
t=text.

I need to sum up the numeric values, based on the text value.

Is there any way I can do this?

Thanks for the help.