From: Cbdavis on
I have one cell that needs to contain the possible sum of cell
(A1*B1) or (C1*D1) or (E1*F1) but only one of the 3 possibles
For example :
A1 is $10.00 B1 is QTY 1 Sum is $10.00
C1 is $8.00 D1 is QTY 1 Sum is $8.00
E1 is $7.00 F1 is QTY 1 Sum is $7.00

Also would be nice if I could allow only 2 of the cells to contain numbers
at a time
(A1 and B1) or (C1 And D1) ETC and not allow more than one set to give the
sum


From: Jacob Skaria on
--You can try the below formula. (all in one line)

=IF(COUNT(A1:B1)=2,A1*B1,IF(COUNT(C1:D1)=2,C1*D1,
IF(COUNT(E1:F1)=2,E1*F1,0)))

--Also to make sure you have only two numbers entered you can set data
validation for this range.. Select A1:F1 . From menu Data>Validation>select
'Custom' and copy paste the below formula.

=COUNT($A$1:$F$1)<3

If this post helps click Yes
---------------
Jacob Skaria


"Cbdavis" wrote:

> I have one cell that needs to contain the possible sum of cell
> (A1*B1) or (C1*D1) or (E1*F1) but only one of the 3 possibles
> For example :
> A1 is $10.00 B1 is QTY 1 Sum is $10.00
> C1 is $8.00 D1 is QTY 1 Sum is $8.00
> E1 is $7.00 F1 is QTY 1 Sum is $7.00
>
> Also would be nice if I could allow only 2 of the cells to contain numbers
> at a time
> (A1 and B1) or (C1 And D1) ETC and not allow more than one set to give the
> sum
>
>
From: Cbdavis on
That worked Great !
What if I want to continue that formula in the next rows below that
throughout the sheet?



"Jacob Skaria" wrote:

> --You can try the below formula. (all in one line)
>
> =IF(COUNT(A1:B1)=2,A1*B1,IF(COUNT(C1:D1)=2,C1*D1,
> IF(COUNT(E1:F1)=2,E1*F1,0)))
>
> --Also to make sure you have only two numbers entered you can set data
> validation for this range.. Select A1:F1 . From menu Data>Validation>select
> 'Custom' and copy paste the below formula.
>
> =COUNT($A$1:$F$1)<3
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Cbdavis" wrote:
>
> > I have one cell that needs to contain the possible sum of cell
> > (A1*B1) or (C1*D1) or (E1*F1) but only one of the 3 possibles
> > For example :
> > A1 is $10.00 B1 is QTY 1 Sum is $10.00
> > C1 is $8.00 D1 is QTY 1 Sum is $8.00
> > E1 is $7.00 F1 is QTY 1 Sum is $7.00
> >
> > Also would be nice if I could allow only 2 of the cells to contain numbers
> > at a time
> > (A1 and B1) or (C1 And D1) ETC and not allow more than one set to give the
> > sum
> >
> >
From: Jacob Skaria on
--Copy the IF() formula down..

--Select the range and set the data validation...Slight change in the formula
=COUNT($A1:$F1)<3

If this post helps click Yes
---------------
Jacob Skaria


"Cbdavis" wrote:

> That worked Great !
> What if I want to continue that formula in the next rows below that
> throughout the sheet?
>
>
>
> "Jacob Skaria" wrote:
>
> > --You can try the below formula. (all in one line)
> >
> > =IF(COUNT(A1:B1)=2,A1*B1,IF(COUNT(C1:D1)=2,C1*D1,
> > IF(COUNT(E1:F1)=2,E1*F1,0)))
> >
> > --Also to make sure you have only two numbers entered you can set data
> > validation for this range.. Select A1:F1 . From menu Data>Validation>select
> > 'Custom' and copy paste the below formula.
> >
> > =COUNT($A$1:$F$1)<3
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "Cbdavis" wrote:
> >
> > > I have one cell that needs to contain the possible sum of cell
> > > (A1*B1) or (C1*D1) or (E1*F1) but only one of the 3 possibles
> > > For example :
> > > A1 is $10.00 B1 is QTY 1 Sum is $10.00
> > > C1 is $8.00 D1 is QTY 1 Sum is $8.00
> > > E1 is $7.00 F1 is QTY 1 Sum is $7.00
> > >
> > > Also would be nice if I could allow only 2 of the cells to contain numbers
> > > at a time
> > > (A1 and B1) or (C1 And D1) ETC and not allow more than one set to give the
> > > sum
> > >
> > >
From: Cbdavis on
I can not get the validation to work correctly.

It will not allow me to enter any value when I use the function below
=COUNT($A$1:$F$1)<3

Is it because there are cells in between the range I am using?
I would like to have data entered in either F7, H7or J7
There is data in E7, G7 and I7 already

"Jacob Skaria" wrote:

> --You can try the below formula. (all in one line)
>
> =IF(COUNT(A1:B1)=2,A1*B1,IF(COUNT(C1:D1)=2,C1*D1,
> IF(COUNT(E1:F1)=2,E1*F1,0)))
>
> --Also to make sure you have only two numbers entered you can set data
> validation for this range.. Select A1:F1 . From menu Data>Validation>select
> 'Custom' and copy paste the below formula.
>
> =COUNT($A$1:$F$1)<3
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Cbdavis" wrote:
>
> > I have one cell that needs to contain the possible sum of cell
> > (A1*B1) or (C1*D1) or (E1*F1) but only one of the 3 possibles
> > For example :
> > A1 is $10.00 B1 is QTY 1 Sum is $10.00
> > C1 is $8.00 D1 is QTY 1 Sum is $8.00
> > E1 is $7.00 F1 is QTY 1 Sum is $7.00
> >
> > Also would be nice if I could allow only 2 of the cells to contain numbers
> > at a time
> > (A1 and B1) or (C1 And D1) ETC and not allow more than one set to give the
> > sum
> >
> >