AND function?

[leonidas]

Hi,

In the formula below the AND function cannot recognise the named range
"KolomFFundering". I need the named range because when I insert a row
the range must also change. Is there any way to rewrite this formula
without losing it's function? The range of "KolomFFundering" is
F13:F23.

=IF(AND(J12<>"";KolomFFundering<>"");"verwijder getal uit kolom
'hoeveelheid'";IF(KolomFFundering<>"";SUM(KolomFFundering);IF(P12<>"";P12/L$227;"")))

Thanks in advance!

 

[Bob Phillips]

=IF(AND(J12<>"";COUNTIF(KolomFFundering,"<>")>0);
"verwijder getal uit kolom'hoeveelheid";
IF(KolomFFundering<>"";SUM(KolomFFundering);IF(P12<>"";P12/L$227;"")))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[leonidas]

Hi Bob,

The COUNTIF function also gives an error #VALUE! and even the I
function gives this error. Is there any solution to this problem

 

[Bearacade]

Am I missing something, should the ;s be ,s?

And I am not sure exactly what "<>" in the countif does for you. I a
assuming that you are looking for non blanks?

Try the following...


=IF(AND(J12<>"",COUNTBLANK(KolomFFundering)>0),"verwijder getal ui
kolom'hoeveelheid",IF(KolomFFundering<>"",SUM(KolomFFundering),IF(P1
<>"",P12/L$227,""))

 

[leonidas]

Hi,

The ;s are needed for VBA in Excel (VB editor). The COUNTBLANK function
gives the same result as the COUNTIF function.
Problem still is that a named range is not recognised by the IF
function, the AND function, the COUNTIF function and the COUNTBLANK
function.
Is there any solution for this?

 

[Bob Phillips]

It does work, see http://cjoint.com/?gwuTy1PrA8

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[leonidas]

Hi Bob,

Your example works indeed, but I think I figured out what the problem
is. Change the named range in your example to F13:F23 and you example
won't work either. The named range has tot start from the first row. So
range F1:F23 will work too. This is a weird thing in Excel I think.
Do you know any solution for this? (If there is one)

 

[leonidas]

Hi Bob,

I now know what the real problem is. The formula works really well, but
I've found the problem. There are formulas in the named range F13:F23.
Those formulas are:
=IF(P13<>"";P13/L$231;"")
=IF(P14<>"";P14/L$231;"")
=IF(P15<>"";P15/L$231;"")
and so on.
Is there any solution for this problem?
Thanks a lot for all your help!

 

[leonidas]

Hi Bob,

I now know what the real problem is. The formula works really well, but
I've found the problem. There are formulas in the named range F13:F23.
Those formulas are:
=IF(P13<>"";P13/L$231;"")
=IF(P14<>"";P14/L$231;"")
=IF(P15<>"";P15/L$231;"")
and so on.
Is there any solution for this problem?
Thanks a lot for all your help!

 

[leonidas]

Hi Bob,

I've found the problem after trying some changes. The formula now works
as intended. The formula now is:

=IF(AND(J12<>"";COUNTIF(KolomFFundering;">0"));
"verwijder getal uit kolom 'hoeveelheid'";
IF(COUNTIF(KolomFFundering;">0");SUM(KolomFFundering);IF(P12<>"";P12/L$231;"")))

Thanks again for all your help!

 

[Bob Phillips]

That should not make any difference. Why do you think that that is a
problem?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)