Sumif contains formula

  • Thread starter Thread starter LiAD
  • Start date Start date
L

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

thanks a lot

Niek Otten said:
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
Click to expand...
 
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
 
got it

thanks

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