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

Status
Not open for further replies.

#### scubafrog79

##### Programmer
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.

- 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

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!

Using the Excel image that you provided...

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!

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

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!

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))

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!

Status
Not open for further replies.

Replies
7
Views
150
Replies
3
Views
132
Replies
2
Views
84
Replies
6
Views
101
Replies
24
Views
227