Excel sum question with if and operators

February 26, 2014 3:43 PM

Spread sheet contains 4 columns. Submitals, interviews, jobs and a total sum column of the prior three cells mentioned. The trick is, a submital is worth 1 point, a intrerview 3 points, a job 5 points. But the value must be 1 each in its respective column, and the sum column must contain the total number of points. How would you format a sheet to achieve this.
posted by cheechman85 to Computers & Internet (2 answers total)

Best answer: For row 2, the value in the total column (cell D2) is "=A2+3*B2+5*C2". Then fill the cells in the total column as needed to repeat the formula.
posted by stopgap at 3:46 PM on February 26, 2014 [3 favorites]

Best answer: For columns A to C, if you want to constrain the input to only 1, or either zero or one, select Data>Validation and on the Settings Tab:
Validation criteria
Allow: "Whole Number"
Data: "equal to"
Value: "1"
to accept only blank or "1" values in the cell

or

Allow: "Whole Number"
Data: "between"
Minimum: "0"
Maximum: "1"
to accept only blank, "0", or "1" values in the cell

stopgap has the most efficient way of tabulating the total cell.
posted by Yorrick at 6:02 PM on February 26, 2014