sum function

  • Thread starter Thread starter Vijay Dhawan
  • Start date Start date
V

Vijay Dhawan

Hi,

How can I sum the numbers in a column in Excel where some numbers carry a
"<" sign in front of them (such as <1) and I want to include numbers such
"<1" in calculation as "1".

For Example if numbers are entered as follows

0.5
<1
0.5

I would like to value to be returned as "2" when I apply "sum" function.

Thanks
 
Try this array formula** :

=SUM(IF(LEFT(A2:A10)="<",--MID(A2:A10,2,10),A2:A10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Try the array formula (press CTRL-SHIFT-ENTER after typing/pasting the formula)
=SUM(IF(LEFT(A1:A3,1)="<",MID(A1:A3,2,255)*1,A1:A3))

Adjust the range A1:A3 to the data you have. I have assumed that you have to
check for < sign only.
 
Hello Mama,

It is nice to see, after a long time, me, you, David and Biff responding to
the same post...

Vijay might have a <2 or <11 in his data...
 
Back
Top