Please Help!!

[ant]

Hello all,

I hope this question makes sense. I have a column that is filled with numbers coming from an IF statement.

The IF statement either returns a value or "".

I want to count the number of cells that have values, and not the "", but I keep getting "" returned as a value.

Do any of you gurus know how to count just the cells with numbers in them and/or turn the "" into something else that is not counted in the COUNT formula???

THANKS!!

[Mark O'Brien]

Why not use the COUNTIF function?

[sen_edp]

Hi Ant

You can use the counta() function

this function count only cells with values,text,or logical values (true or false)

Hope that helps

[IML]

something seems fishy here. You only want to count the numbers and by default count only counts numbers. Are you sure you're populated with a "" and your range is okay???

[Aladin Akyurek]

COUNT ignores "" even if generated by a formula and cannot return "" as result.

[ant]

I've been looking for guys like you!!

Thanks for the suggestions.

I believe my range is correct.

I think Count.if sounds right, but I must be employing it wrong since it is a new function for me.

Basically, I am trying to get a total count; I imagine the process would go:
1) Check the value in the cell.
2) If the value is "", dont count it, otherwise, count it and move to the next cell.
3) At the end of say 25 cells, I should have the total of all the cells with actual values.

Is this something for VBA or can the count.if statement check one cell, store its value, then move to the next...

Thanks again!

[Aladin Akyurek]

On 2002-05-10 14:33, ant wrote:
I've been looking for guys like you!!

Thanks for the suggestions.

I believe my range is correct.

I think Count.if sounds right, but I must be employing it wrong since it is a new function for me.

Basically, I am trying to get a total count; I imagine the process would go:
1) Check the value in the cell.
2) If the value is "", dont count it, otherwise, count it and move to the next cell.
3) At the end of say 25 cells, I should have the total of all the cells with actual values.

Is this something for VBA or can the count.if statement check one cell, store its value, then move to the next...

Thanks again!

Microsoft Excel - COUNT.xls
File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
E5		=
*	A	B	C	D
1	Numbers	*	Result of Counting	*
2	:alert('=IF(B2,1,"")')>	*	:alert('=COUNT(A2:A6)')>3	*
3	1	*	*	*
4	3	*	*	*
5	:alert('=IF(B2,1,"")')>	*	*	*
6	12	*	*	*
7	*	*	*	*
Sheet1

You can see the formula of cells only click each above hyperlinks

The above image was automatically written by excel VBA. If you want this code, click here and I'll email the file to you.


This is intended to convince you that COUNT really ignores blanks.

A2 and A5 are blanks, generated using an IF formula.

[ant]

I think I see my problem now.

My if statement: =SE(H14="sales";U14;"") is returning - in the cell for "".

Is this a formatting problem??

Thanks again.

[sen_edp]

my example works

Microsoft Excel - HtmlMaker.xls
File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
A1		=
	A	B	C	D	E
1
2
3		sales	:alert('12345')>=IF(B3="sales",C11,"")
4		no sales	:alert('')>=IF(B4="sales",C12,"")
5
6
7
8
9
10
11			12345
12
Sheet1

You can see the value of cells only click each above hyperlinks

[Aladin Akyurek]

On 2002-05-10 15:12, ant wrote:
I think I see my problem now.

My if statement: =SE(H14="sales";U14;"") is returning - in the cell for "".

Is this a formatting problem??

Thanks again.

What is the value in U14?