| Home |
| Search |
| Today's Posts |
|
#1
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
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: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 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
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: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 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
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: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 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
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: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 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
| join data in multiple cells when adjoining cells match criteria ?? | Excel Worksheet Functions | |||
| Cells won't change font color or show hi-lighted cells in document | Excel Discussion (Misc queries) | |||
| Setting of input cells as blue font and formula cells as black fon | Excel Discussion (Misc queries) | |||
| trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions | |||
Linear Mode
