Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Formula for checking cells for value and then subtract value 1

Status
Not open for further replies.

scubafrog79

Programmer
Oct 10, 2014
13
US
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.

test_qtomer.png


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

Tt-copy_gsn3du.png


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

Part and Inventory Search

Sponsor

Back
Top