Multiple cell reference IF function

C

Colinhp

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.
 
M

Max

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))
 
R

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)
 
C

Colinhp

Hi Roger,

Thank you for your help.

Roger Govier said:
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)
Click to expand...
 
R

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))
 
C

Colinhp

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.
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

2 Columns total division 4
formula to make the value in a cell, to a row in a sum function 5
Populate multiple cells off one equation 8
a data validation list that contains a list of named ranges - not alist from a named range 1
Utilizng full AutoFilter features while specific cells are protect 0
function returning SUM of 2 adjacent cols in 2 separate cells 7
Utilizng full AutoFilter features while specific cells are protect 0
Setting a cell with a value as a reference for another function 1

Top