Sumif contains formula

[LiAD]

Hi,

I have list of data that contains items made of numbers and numbers+T.
Example

5
500T
0.3
0.21T
158987T
1
8
7
663
12.45T

What formula can I use to Sumif the number contains T and no calculate the
other values at all? So the answer i need for the above list is 159499.66.

Thanks
LiAD

 

[Niek Otten]

Enter the following formula as an array formula, that is, confirm with
CTRL+SHIFT+ENTER unstead of just with ENTER

=SUM(IF(RIGHT(A1:A10,1)="T",VALUE(LEFT(A1:A10,LEN(A1:A10)-1)),0))

If you did this correctly, the formula will show in curly brackets, to show
that it is an array formyla

 

[LiAD]

lovely

thanks a lot

Enter the following formula as an array formula, that is, confirm with
CTRL+SHIFT+ENTER unstead of just with ENTER

=SUM(IF(RIGHT(A1:A10,1)="T",VALUE(LEFT(A1:A10,LEN(A1:A10)-1)),0))

If you did this correctly, the formula will show in curly brackets, to show
that it is an array formyla

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

 

[LiAD]

Hi Nick,

Sorry I thought that if i had the base for this formula I could develop it
to accept more conditions. I actually have four conditions that I need to
use to calculate the sum.

The total test is to sum cells that = A in col A, = B col B, C in col C and
have a T in the number of col F (the formula you gave me).

How can i adapt your formula to cope with several conditions?

Thanks a lot again
LiAD

 

[LiAD]

got it

thanks

Hi Nick,

Sorry I thought that if i had the base for this formula I could develop it
to accept more conditions. I actually have four conditions that I need to
use to calculate the sum.

The total test is to sum cells that = A in col A, = B col B, C in col C and
have a T in the number of col F (the formula you gave me).

How can i adapt your formula to cope with several conditions?

Thanks a lot again
LiAD