URGENT! I really need an Answer!

[jpbonono]

The conditions are:

There are multiple reference cells and all of those should be number, if one becomes a letter or not a number, it would prompt, "ERROR" or "FALSE" or "INVALID", if in case all cells are numbers, it would proceed to SUM it all up.

I've tried IF with ISNUMBER function but it only works with a single cell/value.

Please help.

Thanks.

 

[Aladin Akyurek]

@Aladin, P6,AD6,AR6,BF6. These cells should only contain numbers, if that so, then it will be added. While if one of this cell has changed into letter or not a number, it'll prompt Invalid or Error. Hope this would help.

Try...

=IF(COUNT(P6,AD6,AR6,BF6)=AREAS((P6,AD6,AR6,BF6)),
   SUM(P6,AD6,AR6,BF6),"invalid data encountetered")

 

[jpbonono]

THANK YOU SO MUCH! it works perfectly!

 

[jpbonono]

Could you please help me on my other thread?

 

[Aladin Akyurek]

THANK YOU SO MUCH! it works perfectly!

You are welcome.

Could you please help me on my other thread?

See that thread for a possible solution.

 

[jamilm]

very good. i never could imagine that areas function could be this useful sometimes. from what i understood, you used this areas function becuase the condition were to check multiple ranges.
what if we have similar case but the range is only single range, for example i tried it with the code =IF(COUNT(A4:A12)=AREAS((A4:A12)), SUM(A4:A12),"invalid data encountetered") but it does not work. any idea?

Try...

=IF(COUNT(P6,AD6,AR6,BF6)=AREAS((P6,AD6,AR6,BF6)),
   SUM(P6,AD6,AR6,BF6),"invalid data encountetered")

 

[Aladin Akyurek]

very good. i never could imagine that areas function could be this useful sometimes. from what i understood, you used this areas function becuase the condition were to check multiple ranges.
what if we have similar case but the range is only single range, for example i tried it with the code =IF(COUNT(A4:A12)=AREAS((A4:A12)), SUM(A4:A12),"invalid data encountetered") but it does not work. any idea?

When the range of interest is contiguous, we can do just with COLUMNS or ROWS as the case may be...

=IF(COUNT(A4:A12)=ROWS(A4:A12), SUM(A4:A12),"invalid data encountetered")

 

[jamilm]

GREAT! worked perfectly. i tried it with the columns instead of rows and it worked too.

thanks Aladin.

When the range of interest is contiguous, we can do just with COLUMNS or ROWS as the case may be...

=IF(COUNT(A4:A12)=ROWS(A4:A12), SUM(A4:A12),"invalid data encountetered")

 

[Aladin Akyurek]

GREAT! worked perfectly. i tried it with the columns instead of rows and it worked too.

thanks Aladin.

You are welcome.

 

[Smitty]

Its not sir, the other one is regarding an exceeding of number from the ref cell, this one is if the group of cell should only contain numbers and if one has letter or not a number it would prompt invalid. If the conditions were met the numbers on the cell would be added.

I would suggest trying to use descriptive thread titles in the future. The "URGENT" crap is just as likely to result in people ignoring your question as it is entice them to look at it.

 

[RobertCotton]

If all of the cells will always be populate with either a number or text then this formula should work...

=IF(OR(ISTEXT(P6),ISTEXT(AD6),ISTEXT(AR6),ISTEXT(BF6)),"ERROR",SUM(P6,AD6,AR6,BF6))

If any of the cells can be blank/null OR text then you would probably want to use this...

=IF(AND(ISNUMBER(P6),ISNUMBER(AD6),ISNUMBER(AR6),ISNUMBER(BF6)),SUM(P6,AD6,AR6,BF6),"ERROR")

I hope this helps