Summing values

N

neil

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.
 
P

Peo Sjoblom

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

Mangesh Yadav

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
 
N

neil

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
 
M

Mangesh Yadav

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

Mangesh


neil said:
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
Click to expand...
 
Top