|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 8th, 2009, 03:37 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
Different cells that need to sum in one
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 
|
|
#2
November 8th, 2009, 04:56 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Different cells that need to sum in one
--You can try the below formula. (all in one line)
=IF(COUNT(A1:B1)=2,A1*B1,IF(COUNT(C1  1)=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 DataValidationselect '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
|
|
#3
November 8th, 2009, 02:00 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Different cells that need to sum in one
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 1)=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 DataValidationselect '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
|
|
#4
November 8th, 2009, 02:54 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Different cells that need to sum in one
--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 1)=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 DataValidationselect '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
|
|
#5
November 8th, 2009, 03:10 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Different cells that need to sum in one
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 1)=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 DataValidationselect '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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
