Subtract two columns, but only for cells identified by if statemen

  • Thread starter Thread starter Amanda
  • Start date Start date
A

Amanda

I have 3 columns of information, A, B, C. C is defined by an if statement
if(B<A,"yes", "").
Now I would like to find the difference between A & B, for all the cells in
the column that received a "yes" and add to one total.

Is this possible?
 
I'm assuming you want this difference plus one to be in another column...

=IF(C1="yes",B1-A1+1,"")

or, if you want the formula to only reference A and B, then use the
condition that produced the "yes" value...

=IF(B1<A1,B1-A1+1,"")

Rick
 
=SUMPRODUCT(--(A2:A200>B2:B200), A2:A200-B2:B200)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thank you, but I'm looking for something a little different.
I would like it to add up the differences into one total at the bottom (I
don't want individual differences for each row). Does that make since?

Say I have 4 rows of info....and only two of them are "yes" in column C, I
would like for it to add the two differences into 1 total at the bottom of
column C.
 
Hmm! I see Bob read your request differently than I did. I assumed you want
the calculations on a row-by-row basis whereas he assumed you wanted them in
toto. In re-reading your post, I am still not sure which you are after.

Rick
 
I'm sorry...I've been staring at this for so long, I confused myself....

This is almost what I want...and I tried to edit it to get what I
wanted...but that didn't work. It is actually not two different columns of
info, rather it is one number, say 7.25. If 7.25>A2:A63, if it is a larger
number, then I want to add up the difference between 7.25 and A into one
total at the bottom.

Like I said I tried to edit your formula below, but the anser it gave me, is
not the same as when I calculate it manually. I'm sure I am missing
something real easy.
 
I am looking for one total, but I only want it to add up those cells that fit
my if criteria, i.e. 7.25>A. It is not two separate columns either....it is
one fixed number.
 
Okay, I think Bob has given you the formula you need. I just re-read your
post again and see I misinterpreted your...

Now I would like to find the difference between A & B,
for all the cells in the column that received a "yes" and add
to one total

as asking for one to be added to the difference (which is what led me to
conclude you wanted a row-by-row solution).

Rick
 
=SUMPRODUCT(--(A2:A20>7.25),A2:A20-7.25)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thank you so much for your patience....
I updated my info using the below, however the total I am getting is not the
same as manually calculating it.... Am I suppose to the replace the -- with
something?
 
Absolutely not!

Can you post the data so that I can check it through?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
I tried to sign back on yesterday to let you know that it did work. I
reviewed my columns and didn't have all of the cells in the formula.

Thanks a million for your help!

Question though....what is the --? Is that just like writing "if"?
 
Back
Top