Multiple cell reference IF function

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.

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)

--
Regards

Roger Govier


"Colinhp" <[email protected]> wrote in message
news:[email protected]...
>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.

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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Colinhp" wrote:
> 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.

Hi Roger,

Thank you for your help.

"Roger Govier" wrote:

> 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)
>
> --
> Regards
>
> Roger Govier
>
>
> "Colinhp" <[email protected]> wrote in message
> news:[email protected]...
> >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.
>
>
>

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


--
Regards

Roger Govier


"Colinhp" <[email protected]> wrote in message
news:[email protected]...
> Hi Roger,
>
> Thank you for your help.
>
> "Roger Govier" wrote:
>
>> 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)
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Colinhp" <[email protected]> wrote in message
>> news:[email protected]...
>> >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.
>>
>>
>>

"Roger Govier" wrote:
> .. =IF(COUNTA(Mydata)<12,"You need more data",SUM(Mydata))

Nice, neat solution, Roger !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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.

"Max" wrote:

> "Roger Govier" wrote:
> > .. =IF(COUNTA(Mydata)<12,"You need more data",SUM(Mydata))
>
> Nice, neat solution, Roger !
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

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

--
Regards

Roger Govier


"Colinhp" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> "Max" wrote:
>
>> "Roger Govier" wrote:
>> > .. =IF(COUNTA(Mydata)<12,"You need more data",SUM(Mydata))
>>
>> Nice, neat solution, Roger !
>> --
>> Max
>> Singapore
>> http://savefile.com/projects/236895
>> xdemechanik
>> ---