×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

Formula for checking cells for value and then subtract value

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close