Different cells that need to sum in one
in [General]
|
From: Cbdavis on 7 Nov 2009 22:37 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 7 Nov 2009 23:56 --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 8 Nov 2009 09:00 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 8 Nov 2009 09:54 --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 8 Nov 2009 10:10 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 > > > >
|
Pages: 1 Prev: Convert SPI Open Access Spreadsheet Next: Pivot Table Calculated Field - Sum/count |