AND function?
 

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(KolomFFu ndering);IF(P12<"";P12/L$227;"")))

Thanks in advance!


--
leonidas
------------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=554633

AND function?
 

=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" wrote in
message ...

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(KolomFFu ndering);IF(P12<"";P12/L$
227;"")))

Thanks in advance!


--
leonidas
------------------------------------------------------------------------
leonidas's Profile:
http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=554633

AND function?
 

Hi Bob,

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


--
leonidas
------------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=554633

AND function?
 

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

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

Try the following...


=IF(AND(J12<"",COUNTBLANK(KolomFFundering)0),"ve rwijder getal uit
kolom'hoeveelheid",IF(KolomFFundering<"",SUM(Kolo mFFundering),IF(P12
<"",P12/L$227,"")))


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=554633

AND function?
 

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?


--
leonidas
------------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=554633

AND function?
 

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

--
HTH

Bob Phillips

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

"leonidas" wrote in
message ...

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?


--
leonidas
------------------------------------------------------------------------
leonidas's Profile:
http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=554633

AND function?
 

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
------------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=554633

AND function?
 

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 a
=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
------------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=554633

AND function?
 

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 a
=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
------------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=554633

AND function?
 

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(KolomFFunderi ng);IF(P12<"";P12/L$231;"")))

Thanks again for all your help!


--
leonidas
------------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=554633

AND function?
 

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)

"leonidas" wrote in
message ...

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 a
=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
------------------------------------------------------------------------
leonidas's Profile:
http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=554633