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

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?

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

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


"Amanda" wrote in message
...
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?

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

=SUMPRODUCT(--(A2:A200B2:B200), A2:A200-B2:B200)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Amanda" wrote in message
...
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?

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

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.

"Rick Rothstein (MVP - VB)" wrote:

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


"Amanda" wrote in message
...
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?

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

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"Amanda" wrote in message
...
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?

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

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.25A2: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.

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A200B2:B200), A2:A200-B2:B200)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Amanda" wrote in message
...
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?

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

I am looking for one total, but I only want it to add up those cells that fit
my if criteria, i.e. 7.25A. It is not two separate columns either....it is
one fixed number.

"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"Amanda" wrote in message
...
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?

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

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


"Amanda" wrote in message
...
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.

"Rick Rothstein (MVP - VB)" wrote:

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


"Amanda" wrote in message
...
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?

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

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?

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A207.25),A2:A20-7.25)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Amanda" wrote in message
...
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.25A2: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.

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A200B2:B200), A2:A200-B2:B200)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Amanda" wrote in message
...
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?

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

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)



"Amanda" wrote in message
...
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?

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A207.25),A2:A20-7.25)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Amanda" wrote in message
...
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.25A2: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.

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A200B2:B200), A2:A200-B2:B200)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Amanda" wrote in message
...
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?

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

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

"Bob Phillips" wrote:

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)



"Amanda" wrote in message
...
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?

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A207.25),A2:A20-7.25)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Amanda" wrote in message
...
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.25A2: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.

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A200B2:B200), A2:A200-B2:B200)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Amanda" wrote in message
...
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?

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

No, it is forcing some value into its numeric equivalent. See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
discussion.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Amanda" wrote in message
...
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"?

"Bob Phillips" wrote:

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)



"Amanda" wrote in message
...
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?

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A207.25),A2:A20-7.25)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Amanda" wrote in message
...
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.25A2: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.

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A200B2:B200), A2:A200-B2:B200)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Amanda" wrote in message
...
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?