Formula for checking cells for value and then subtract value 1

[scubafrog79]

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.

 

[Andrzejek]

- I have a value of 1,000,000 in cell [red]B2[/red]
- my value in cell [red]B2[/red] 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 [red]B2[/red]
- my value in cell [red]B2[/red] 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 [red]B2[/red]
- my value in cell [red]B2[/red] 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

 

[SkipVought]

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

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

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[scubafrog79]

Using the Excel image that you provided...

 

[SkipVought]

If you take the time to understand the "TIP" I posted, that uses as a criteria, <>"No", you might be able to figure out how to get the sum for the criteria "
="No", or at least try.

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[scubafrog79]

I must have done something wrong with the formula the first time because it didn't work, but now it does.

Thanks for the help

 

[SkipVought]

Comeon now, scubafrog79. Our members will want to know your solution. That's how it's supposed to work here at Tek-Tips. Please tell us your solution.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[scubafrog79]

I mistyped the formula the first time once I realized that I corrected my mistake.

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

 

[SkipVought]

FYI, the SUMPRODUCT() function, in this format, can be used to count or sum table data using multiple criteria, not just a single criteria as demonstrated above.

The SUMPRODUCT() function can be a very powerful tool for data analysis.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!