If there are values in specific cells, I need them to subtract from one main cell

I need to find an equation so if there are any values from B2-R2 it will subtract 1 from W2 which needs to start at a value of 17.

For example, if the value of D2 is 6 it will subtract 1 from W2. Then if there is a value of 3 in H2, it will subtract 1 from W2's new value.

So if there is a value in B2 through R2, the value of W2 should be 0.

I'm new to excel and am having a hard time figuring this out. Thanks

Russ

Posted 2014-11-21T20:09:19.087

Reputation: 1

Answers

You might want

=17-COUNTA(B2:R2)

where COUNTA() counts the non-empty (non-blank) cells in a range. But part of your question makes it sound like you are putting a value of 17 into cell W2 and then modifying (decrementing) that cell. That's not possible with formulas; to actually change a cell you need to use VBA.

G-Man Says 'Reinstate Monica'

Posted 2014-11-21T20:09:19.087

Reputation: 6 509

1Anybody who has a different answer *should post a different answer*. If your answer uses a function whose operation is less obvious than COUNT (something like SUMPRODUCT, for example), include an explanation of how it works in your answer. – G-Man Says 'Reinstate Monica' – 2014-11-21T21:16:00.600

1@Russ - G-Man makes a good point about VBA. To expand a little, if you are new to Excel, you may think of solutions in terms of how you would manually approach the problem. There are two ways to get a value into a cell. You can pre-place a formula, which will update as the source data changes, or you can place a value into a cell or manipulate it in real time with VBA as the result of some trigger action. If you only care that the result in W2 reflects what is in B2:R2, even if that changes, you can use formulas. If you need to control when or how W2 gets updated, you would use VBA. – fixer1234 – 2014-11-22T05:18:06.963

If the cells are truly blank or if they contain formulas that may return empty strings (e.g. "") then the COUNTBLANK function has been available since at least Excel 2003. COUNTBLANK will count an empty string as a blank while COUNTA will count the empty string as a value. In W2 use the following formula.

=COUNTBLANK(B2:R2)

When all of the cells in the B2:R2 range are blank (or empty strings) the result will be 17. As any cell in that range gets a value typed in (or a formula produce s a result) the 17 gets reduced by 1 for each cell.

Jeeped

Posted 2014-11-21T20:09:19.087

Reputation: 2 435