C1 C2 C3 C4 C5

[workshop]

C1 C2 C3 C4 C5 C6
R1 1 1 1
R2 1 1 1 1
R3 1
R4
R5 1 1 1

in the C6 R1 I use =if(counta(C1:C5)>1,counta(C1:5),"&quot It's give me a number of 3, R2 number of 4..., but in the R4 it's give me a number of "5" when it's suppose to be "0"... What do I do wrong?? please help

 

[donnadee]

Maybe there's a formula to sum in one of the columns it is adding over to your C6 column.

Can you click in each of the cells in R4 to see if there is some kind of formula?

 

[Dreamboat]

I don't know why you're using COUNTA.

Sounds like you should be using:

=IF(COUNT(A1:E1)>1,COUNT(A1:E1),"&quot

COUNTA is used when you're counting cells that contain text.

http://www.PiersonTech.com

techsupportgirl@home.com
Brainbench MVP for Microsoft Word

 

[workshop]

Hi Dreamboat,
It's didn't matter what I use it's still come out wrong, All data from C1 to c5 is the sum of different sheet, if I type in 1 in from C1 to C5 then C6 come out right, but I have over 8000 rows and data all scatter from C1 to C5. I have try copy that sheet then use paste special as value from C1 R1 to C5 R8000 well c6 don't count but highlight some value and retype in same cell then c6 same row is count...I don't know why

 

[Dreamboat]

Your data is not being seen by Excel as numbers, I suppose. To force it, follow these directions exactly:

Type a 1 in any cell.
Select that cell and hit copy.
Select all of your data cells.
Hit Edit-Paste special-Multiply.

This multiplies the cell contents by 1, which forces Excel to see them as numbers.

Good luck.

http://www.PiersonTech.com

techsupportgirl@home.com
Brainbench MVP for Microsoft Word

 

[workshop]

Thanks Dreamboat, it's seem working but in the cell that don't have 1 then copy cell is #value, I can live with that, but curious is there anyway to leave it blank...
Thank You

 

[Hasit]

The "" in your formula should take care of any values less than 1. Chek your formula and make sure that you give the IF function an option if there is no value in the cell(s).