Multiple cell reference IF function

[Guest]

I want a SUM formula to occur in cell B34 that includes the following cells,
providing they are not blank. O12,O13,O23,O24,O36,O37,O45,O46,O56,O57,O65
and O66. These cells have a drop-down selection of numbers from 1-5, and
have data validation and conditional formatting set to red prior to them
choosing from the drop-down list.
I can nest ISBLANKO12:O13 in an IF function and if False have the SUM
formula occur, but how do I also include these cells
(O12,O13,O23,O24,O36,O37,O45,O46,O56,O57,O65 and O66) in the IF function too?
If any of these cells are left blank by the user I want a message to appear
informing them that they must enter data in all of the cells before they find
a result shown in B34.
This message is not to be shown before the user starts entering data in the
cells.

 

[Guest]

One way ..

Try in B34:
=IF(SUM(COUNTBLANK(O12:O13),COUNTBLANK(O23:O24),COUNTBLANK(O36:O37),COUNTBLANK(O45:O46),COUNTBLANK(O56:O57),COUNTBLANK(O65:O66))>0,"Incomplete
data entry",SUM(O12:O13,O23:O24,O36:O37,O45:O46,O56:O57,O65:O66))

 

[Roger Govier]

Hi Colin

One way would be to create a named range called Mydata using
Insert>Name>Define and including your range of 12 cells as the Refers to
item.
Then
=IF(COUNTA(Mydata)<12,"You need more data",your_formula)

 

[Guest]

Hi Roger,

Thank you for your help.

Hi Colin

One way would be to create a named range called Mydata using
Insert>Name>Define and including your range of 12 cells as the Refers to
item.
Then
=IF(COUNTA(Mydata)<12,"You need more data",your_formula)

 

[Roger Govier]

Hi Colin
Thanks for the feedback.
If I had read your question thoroughly I would have realised what the
formula was that you wanted and my response should have been
=IF(COUNTA(Mydata)<12,"You need more data",SUM(Mydata))

 

[Guest]

.. =IF(COUNTA(Mydata)<12,"You need more data",SUM(Mydata))

Nice, neat solution, Roger !

---

 

[Guest]

Hi,

The nominated cell for the error data is too small for the user to see. Is
there a simple solution to having a message box appear when the If function
returns the message? I have posted this question in the Excel programming
section, prior to me reading your second response. Data Validation appears
not to work as the cell is not selected by the user.

 

[Roger Govier]

Hi Colin
Try changing to
=IF(COUNTA(Mydata)<12,"DATA!!!",SUM(Mydata))
Then apply conditional formatting to cell
Formula Is =NOT(ISNUMBER(cellref)) where cellref is the cell where your
formula is entered
Format Font RED, Bold