count number of cells

[Jonsson]

Hi all,

I have searched for a way to get the answer of how many of cells in
column returning a value from other cells.

I have tried =count(A1:A10;A12:A19;A21:A30) which works fine althoug
the cells containing formulas, but, when having this formula:
=IF(G44<=20;0;IF(AND(G44=C44;F44<=20;G44>20);(G44-20);IF(AND(G44>C44;C44>20);C44-20;IF(AND(G44<C44;G44>20);G44-20;
")))), I dont get the expected result.
I suppose that somewhere in that formula there is a statement that
mess it up.

Whats wrong, and what to do about it to get the right result

Any ideas anyone?

//Thoma

 

[Sandy Mann]

Thomas,

I don't know what result you expected but your formula returns all the
results that I would expect from plugging in various values. One thing
about the formula is that the first test, =IF(G44<=20;0 means that for the
rest of the formula to return anything at all G44 MUST be over 20 therefore
the tests in the AND() tests do not require to test it again. Removing the
second G44 test gives the formula:

=IF(G44<=20;IF(AND(G44=C44;F44<=20);G44-20;IF(AND(G44>C44;C44>20);C44-20;IF
(G44<C44;G44-20;" "))))

Does this help you see why it is not giving you what you expect?

Incidetally, you do not need the space between the quotation at the end of
the formula, "" will return a blank, (but not empty), cell.

Regards

Sandy

 

[Jonsson]

Thanks, but I guess I have to explain a little bit more what I try t
accomplish.

Sandy, I need to have the IF-formula as it is.

My problem is this: I have two columns , I want to count the amount o
cells, returning value if TRUE,and the columns containing two differen
formulas:
Column A:
=IF(G41<=18,25;0;IF(F41>=20;0;IF(AND(G41>18,25;G41<=20;F41<=18,25);G41-18,25;IF(AND(G41>18,25;G41<=20;F41>18,25);G41-F41;IF(AND(G41>20;F41<=18,25);20-18,25;IF(AND(G41>20;F41>18,25;F41<20);20-F41;""))))))
To get the amount of cells that returns value if true: WORKS FINE!

Column Bwhere the problem starts)
=IF(G44<=20;0;IF(AND(G44=C44;F44<=20;G44>20);(G44-20);IF(AND(G44>C44;C44>20)
;C44-20;IF(AND(G44<C44;G44>20);G44-20;"
To get the amount of cells that returns value if true: WORKS BAD!!

The column B containing the specific formula, somehow mess with m
struggle to get the amount of cells that returns values according t
the IF statesments in the formula.

When I use =COUNT to count the cells in column B, containing value i
the cases the IF-statements are true, I don't get the right amount.
But using the =COUNT formula in column A, to count the amount of cells
I get the right amount.

Whats with the formula in column B, that makes the problem with th
COUNT formula. It seems like I get the amount of cells in column
counted wether the formula returns TRUE or not.

Hope someone can understand my problem!

//Thoma

 

[Sandy Mann]

Hi Thomas,

I *think* that I see what you are saying and what the problem is. COUNT
will only return a count of *numbers* it will ignore text, therefore when
the
formula in Column B returns "" COUNT will not count it. Column B's formula
will return "" whenever C44, F44 & G44 are bigger then 20 and all equal.
Without going through all the possibilities I assume that it is either
impossible or unlikely that any set of data will return the "" in the A
Column formula.

Again let me point out using Formula 'A' as an example that if the first
test =IF(G41<=18,25; must fail in order for us to get as far as IF test 3.
Therefore there is no need to test in test 3 "AND(G41>18,25;" because if it
wasn't then test 1 would have passed as true and we would never have got as
far as test 3. See what I mean?

I hope that this is of help

Sandy
--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

Thanks, but I guess I have to explain a little bit more what I try to
accomplish.

Sandy, I need to have the IF-formula as it is.

My problem is this: I have two columns , I want to count the amount of
cells, returning value if TRUE,and the columns containing two different
formulas:
Column A:

=IF(G41<=18,25;0;IF(F41>=20;0;IF(AND(G41>18,25;G41<=20;F41<=18,25);G41-18,25