Countblanks

[GordonP]

I HAVE THE FOLLOWING DATA:

A | B | C | D | E | F |
TYPE | DIAMETER | QTY | METERS | FEET | INCHES | RESULT I want to acheive
a | 50 | 2 | 1 | blank | blank | OK/1
b | 40 | 3 | blank | 2 | 5 | OK/1
c | 32 | 5 | blank | blank | 6 | OK/1
c | 32 | 5 | blank | 6 | BLANK | OK/1
a | 25 | 6 | 1 | 3 | blank | ERROR/0
a | 25 | 6 | blank | blank | blank | ERROR/0
a | 25 | 6 | 2 | blank | 4 | ERROR/0

THEREFORE I WANT A FORMULA THAT TELLS ME THAT COLUMNS A,B,C, AND SUM OF COLUMNS D,E,F IS <0

IF EITHER A, OR, B OR C OR SUM OF D AND E ANF F IS 0 IT RETURNS AN ERROR.

[sandy666]

Fix your CAPS key

[Crooza]

=if(or(a1="",b1="",c1="",sum(d1:f1)=0),"error message","ok message")

[GordonP]

DEAR CROOZA,

Thanks for your reply. i amended abit the formula as follows:

=IF(OR(A44="",B44="",C44="",SUM(D44:F44)<1),"INCOMPLETE","1")

and its working as i want it except for when the whole range that is A44:f44 is empty i want it still to return 1

[Sam Capricci]

you could amend it to this... =IF(OR(A44="",B44="",C44="",sum(A44:c44)<1,SUM(D44:F44)<1),"INCOMPLETE","1")

[José Augusto]

Hi
I suppose that you want only one measure unit. If so you can use
=IF(AND(A3<>"",B3>0,C3>0,COUNT(D3:F3)=1),"Ok/1","ERROR/0")
Note: In Row 4 my result is ERROR/0, not the result you want acheive

[GordonP]

Dear All,

Thanks for your help.

I found out this formula very well to cater for what i need.

=IF(OR(A44="",B44="",C44="",SUM(D44:F44)<1),"INCOMPLETE","1")

except for when the whole range that is A44:f44 is empty i want it still to return 1.

How can i tweet it?

[José Augusto]

Hi
Try

Please Login or Register  to view this content.

=IF(OR(A44="",B44="",C44="",SUM(D44:F44)<1),"INCOMPLETE",IF(AND(A44="",B44="",C44="",D44="",E44="",F44=""),"","1")