I have a problem on hoe to deal with this data:
in a column data are as follows:
5N
4N
3
4
3N and so on
Now i'd like to sum up all numbers with appending text, and counting cells
having "N",and count cells without "N". I hope somebody can urgently help
me..thanks

Hi,

Try something like these 2 array formulas:


=SUM(IF(RIGHT(A1:A10)="n",ABS(LEFT(A1:A10,LEN(A1:A 10)-1))))

and for counting n's use:

=SUM(IF(RIGHT(A1:A10,1)="n",1))

both formula will need to be entered using CTRL+SHIFT+ENTER

Hope this helps!
Jean-Guy
"spidut" wrote:

I have a problem on hoe to deal with this data:
in a column data are as follows:
5N
4N
3
4
3N and so on
Now i'd like to sum up all numbers with appending text, and counting cells
having "N",and count cells without "N". I hope somebody can urgently help
me..thanks

pinmaster,

=SUM(IF(RIGHT(A1:A10)="n",ABS(LEFT(A1:A10,LEN(A1:A 10)-1))))

Why did you include the ABS() function?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"pinmaster" wrote in message
...
Hi,

Try something like these 2 array formulas:


=SUM(IF(RIGHT(A1:A10)="n",ABS(LEFT(A1:A10,LEN(A1:A 10)-1))))

and for counting n's use:

=SUM(IF(RIGHT(A1:A10,1)="n",1))

both formula will need to be entered using CTRL+SHIFT+ENTER

Hope this helps!
Jean-Guy
"spidut" wrote:

I have a problem on hoe to deal with this data:
in a column data are as follows:
5N
4N
3
4
3N and so on
Now i'd like to sum up all numbers with appending text, and counting
cells
having "N",and count cells without "N". I hope somebody can urgently help
me..thanks

If there is only ever only one letter N in the cells then to add up those
cells try:

=SUM(IF(RIGHT(A1:A4)="N",--LEFT(A1:A4,LEN(A1:A4)-1)))

This is an array formula which must be entered with Control and Shift
pressed and held while you press Enter

To add up the other cells then simply =SUM(A1:A4) wll do it because SUM()
ignores text. I know that you didn't ask that but......


To count the number of cells with an N then try:

=COUNTA(A1:A4)-COUNT(A1:A4)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"spidut" wrote in message
...
I have a problem on hoe to deal with this data:
in a column data are as follows:
5N
4N
3
4
3N and so on
Now i'd like to sum up all numbers with appending text, and counting cells
having "N",and count cells without "N". I hope somebody can urgently help
me..thanks

=COUNTIF(A1:A100,"*N*")


"spidut" wrote:

I have a problem on hoe to deal with this data:
in a column data are as follows:
5N
4N
3
4
3N and so on
Now i'd like to sum up all numbers with appending text, and counting cells
having "N",and count cells without "N". I hope somebody can urgently help
me..thanks