×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

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

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

- I have a value of 1,000,000 in cell B2
- 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

#### Quote:

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,

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

(OP)
Using the Excel image that you provided...

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

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,

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

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

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

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,

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

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

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

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,

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!

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!