## Formula for checking cells for value and then subtract value

## Formula for checking cells for value and then subtract value

(OP)

What I want to do is get the sum of one row where I have a "NO" value on another row and then subtract that sum in another cell. Say I have a value of 1,000,000 in cell B2

Then further down in the spreadsheet I have 12 columns with data in two rows.

In this example my value in cell B2 would now be 800,000 since I'm subtracting 200,000 from 1,000,00. However, I want the formula to sum the first row where ever there is a "NO" on the second, there could none or one or multiple on the second.

Then further down in the spreadsheet I have 12 columns with data in two rows.

In this example my value in cell B2 would now be 800,000 since I'm subtracting 200,000 from 1,000,00. However, I want the formula to sum the first row where ever there is a "NO" on the second, there could none or one or multiple on the second.

## RE: Formula for checking cells for value and then subtract value

- my value in cell B2 would now be 800,000 since I'm subtracting 200,000 from 1,000,00

If you keep going, you will get:

- (now) I have a value of 800,000 in cell B2

- my value in cell B2 would now be 600,000 since I'm subtracting 200,000 from 800,000

and then

- (now) I have a value of 600,000 in cell B2

- my value in cell B2 would now be 400,000 since I'm subtracting 200,000 from 600,000

and on, and on, and on...

I don't think you can have this 'circular' reference because you will be going until you run out of memory.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson## RE: Formula for checking cells for value and then subtract value

"further down" ???

Excel deals in ROWS, either in ROW x or y ROWS down.

So if it were in ROW 5&6...

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...unless you multiply yourself by the speed of light squared, then...

You Energy!## RE: Formula for checking cells for value and then subtract value

## RE: Formula for checking cells for value and then subtract value

<>"No", you might be able to figure out how to get the sum for the criteria", or at least try.="No"

You have been given a wonderful, valuable tool. You must explore it and use it in order to reap the benefits.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...unless you multiply yourself by the speed of light squared, then...

You Energy!## RE: Formula for checking cells for value and then subtract value

Thanks for the help

## RE: Formula for checking cells for value and then subtract value

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...unless you multiply yourself by the speed of light squared, then...

You Energy!## RE: Formula for checking cells for value and then subtract value

In the cell that I wanted the formula to work in is: B2-SUMPRODUCT((C12:N12<>"NO")*(C11:N11))

## RE: Formula for checking cells for value and then subtract value

using multiple criteria, not just a single criteria as demonstrated above.The SUMPRODUCT() function can be a very powerful tool for data analysis.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...unless you multiply yourself by the speed of light squared, then...

You Energy!