Hi,
This one is getting the better of me, even though I know I should be
able to figure it out myself!:

I have two columns of data: B and C.
I have one column of ID#: A

I would like to subtract C from B in all rows where ID# is X and
display the sum of these values in a single cell. I would then like to
do the same for all rows where ID# is Y.

Please help!

Many thanks for your time,

Paul

Try these:

=SUMPRODUCT((A1:A100="X")*(B1:B100-C1:C100))

=SUMPRODUCT((A1:A100="Y")*(B1:B100-C1:C100))

Adjust column ranges to suit, but you can't have a complete column
(unless you have XL2007).

Hope this helps.

Pete



On Jan 14, 6:47*am, paulkaye <paulmjk...@gmail.com> wrote:
> Hi,
> This one is getting the better of me, even though I know I should be
> able to figure it out myself!:
>
> I have two columns of data: B and C.
> I have one column of ID#: A
>
> I would like to subtract C from B in all rows where ID# is X and
> display the sum of these values in a single cell. I would then like to
> do the same for all rows where ID# is Y.
>
> Please help!
>
> Many thanks for your time,
>
> Paul

Hi,

I got a #VALUE! error.

I don't quite understand the formula - it looks like it does something
like:

A1*(B1-C1)
+A2*(B2-C2)
+A3*(B3-C3)
....

Could you explain? I was expecting to do something with IF statements!

Many thanks again for your time,

Paul

On Jan 14, 11:37 am, Pete_UK <pashu...@auditel.net> wrote:
> Try these:
>
> =SUMPRODUCT((A1:A100="X")*(B1:B100-C1:C100))
>
> =SUMPRODUCT((A1:A100="Y")*(B1:B100-C1:C100))
>
> Adjust column ranges to suit, but you can't have a complete column
> (unless you have XL2007).
>
> Hope this helps.
>
> Pete
>
> On Jan 14, 6:47 am, paulkaye <paulmjk...@gmail.com> wrote:
>
> > Hi,
> > This one is getting the better of me, even though I know I should be
> > able to figure it out myself!:
>
> > I have two columns of data: B and C.
> > I have one column of ID#: A
>
> > I would like to subtract C from B in all rows where ID# is X and
> > display the sum of these values in a single cell. I would then like to
> > do the same for all rows where ID# is Y.
>
> > Please help!
>
> > Many thanks for your time,
>
> > Paul

The first part of the formula (A1:A100="X") is in fact a conditional
statement which will check in turn if A1="X", A2="X", A3="X" etc and
return TRUE or FALSE as appropriate, which will be interpreted as 1 or
0 respectively. Hence:

(1 or 0)*(B1-C1)
+(1 or 0)*(B2-C2)
+(1 or 0)*(B3-C3)

will give (Bx - Cx) only where Ax = "X", which is what you want.

Check that you have proper numbers in columns B and C, and not text
values. An alternative that you might like to try is:

=SUMPRODUCT(--(A1:A100="X"),(B1:B100-C1:C100))

Hope this helps.

Pete


On Jan 14, 10:08*am, paulkaye <paulmjk...@gmail.com> wrote:
> Hi,
>
> I got a #VALUE! error.
>
> I don't quite understand the formula - it looks like it does something
> like:
>
> * A1*(B1-C1)
> +A2*(B2-C2)
> +A3*(B3-C3)
> ...
>
> Could you explain? I was expecting to do something with IF statements!
>
> Many thanks again for your time,
>
> Paul
>
> On Jan 14, 11:37 am, Pete_UK <pashu...@auditel.net> wrote:
>
>
>
> > Try these:
>
> > =SUMPRODUCT((A1:A100="X")*(B1:B100-C1:C100))
>
> > =SUMPRODUCT((A1:A100="Y")*(B1:B100-C1:C100))
>
> > Adjust column ranges to suit, but you can't have a complete column
> > (unless you have XL2007).
>
> > Hope this helps.
>
> > Pete
>
> > On Jan 14, 6:47 am, paulkaye <paulmjk...@gmail.com> wrote:
>
> > > Hi,
> > > This one is getting the better of me, even though I know I should be
> > > able to figure it out myself!:
>
> > > I have two columns of data: B and C.
> > > I have one column of ID#: A
>
> > > I would like to subtract C from B in all rows where ID# is X and
> > > display the sum of these values in a single cell. I would then like to
> > > do the same for all rows where ID# is Y.
>
> > > Please help!
>
> > > Many thanks for your time,
>
> > > Paul- Hide quoted text -
>
> - Show quoted text -

Ah, thank you for that explanation - now I understand what the formula
is doing. I obviously simplified the spreadsheet description in my
original question but cannot seem to get the correct result. Here is
how I have extended your suggestion:

=SUMPRODUCT((AND($C$6:$C$1000="103",$D$6:$D$1000="120ml")*($I$6:$I
$1000)))

As you can see, there are two conditions. I want to add up the values
in column I for product 103 (column C) in size 120ml (column D). I'm
getting zero as the result every time. Is there an error in the
formula I've entered here? Just for your info, I'm intending to extend
the formula further (by using INDIRECT to reference the formula and
size names) once I've got this correct.

Many thanks for your time,

Paul

On Jan 14, 12:58 pm, Pete_UK <pashu...@auditel.net> wrote:
> The first part of the formula (A1:A100="X") is in fact a conditional
> statement which will check in turn if A1="X", A2="X", A3="X" etc and
> return TRUE or FALSE as appropriate, which will be interpreted as 1 or
> 0 respectively. Hence:
>
> (1 or 0)*(B1-C1)
> +(1 or 0)*(B2-C2)
> +(1 or 0)*(B3-C3)
>
> will give (Bx - Cx) only where Ax = "X", which is what you want.
>
> Check that you have proper numbers in columns B and C, and not text
> values. An alternative that you might like to try is:
>
> =SUMPRODUCT(--(A1:A100="X"),(B1:B100-C1:C100))
>
> Hope this helps.
>
> Pete
>
> On Jan 14, 10:08 am, paulkaye <paulmjk...@gmail.com> wrote:
>
> > Hi,
>
> > I got a #VALUE! error.
>
> > I don't quite understand the formula - it looks like it does something
> > like:
>
> > A1*(B1-C1)
> > +A2*(B2-C2)
> > +A3*(B3-C3)
> > ...
>
> > Could you explain? I was expecting to do something with IF statements!
>
> > Many thanks again for your time,
>
> > Paul
>
> > On Jan 14, 11:37 am, Pete_UK <pashu...@auditel.net> wrote:
>
> > > Try these:
>
> > > =SUMPRODUCT((A1:A100="X")*(B1:B100-C1:C100))
>
> > > =SUMPRODUCT((A1:A100="Y")*(B1:B100-C1:C100))
>
> > > Adjust column ranges to suit, but you can't have a complete column
> > > (unless you have XL2007).
>
> > > Hope this helps.
>
> > > Pete
>
> > > On Jan 14, 6:47 am, paulkaye <paulmjk...@gmail.com> wrote:
>
> > > > Hi,
> > > > This one is getting the better of me, even though I know I should be
> > > > able to figure it out myself!:
>
> > > > I have two columns of data: B and C.
> > > > I have one column of ID#: A
>
> > > > I would like to subtract C from B in all rows where ID# is X and
> > > > display the sum of these values in a single cell. I would then like to
> > > > do the same for all rows where ID# is Y.
>
> > > > Please help!
>
> > > > Many thanks for your time,
>
> > > > Paul- Hide quoted text -
>
> > - Show quoted text -

Hi Paul,

no need for the AND, as the * does the same job - you can re-write it
as:

=SUMPRODUCT(($C$6:$C$1000="103")*($D$6:$D$1000="120ml")*($I$6:$I
$1000))

The only other concern I have is whether your product codes are
numbers or text in column C - you might have to write the first bit
as:

($C$6:$C$1000=103)

You can put the values in different cells, eg M1, N1, and then your
formula becomes:

=SUMPRODUCT(($C$6:$C$1000=M1)*($D$6:$D$1000=N1)*($I$6:$I$1000))

This way you can change the values easily without having to change the
formula. Also, if the formula is in cell O1 then you can put other
values in M and N and copy the formula down.

Hope this helps.

Pete

On Jan 15, 8:27*am, paulkaye <paulmjk...@gmail.com> wrote:
> Ah, thank you for that explanation - now I understand what the formula
> is doing. I obviously simplified the spreadsheet description in my
> original question but cannot seem to get the correct result. Here is
> how I have extended your suggestion:
>
> =SUMPRODUCT((AND($C$6:$C$1000="103",$D$6:$D$1000="120ml")*($I$6:$I
> $1000)))
>
> As you can see, there are two conditions. I want to add up the values
> in column I for product 103 (column C) in size 120ml (column D). I'm
> getting zero as the result every time. Is there an error in the
> formula I've entered here? Just for your info, I'm intending to extend
> the formula further (by using INDIRECT to reference the formula and
> size names) once I've got this correct.
>
> Many thanks for your time,
>
> Paul
>
> On Jan 14, 12:58 pm, Pete_UK <pashu...@auditel.net> wrote:
>
>
>
> > The first part of the formula (A1:A100="X") is in fact a conditional
> > statement which will check in turn if A1="X", A2="X", A3="X" etc and
> > return TRUE or FALSE as appropriate, which will be interpreted as 1 or
> > 0 respectively. Hence:
>
> > *(1 or 0)*(B1-C1)
> > +(1 or 0)*(B2-C2)
> > +(1 or 0)*(B3-C3)
>
> > will give (Bx - Cx) only where Ax = "X", which is what you want.
>
> > Check that you have proper numbers in columns B and C, and not text
> > values. An alternative that you might like to try is:
>
> > =SUMPRODUCT(--(A1:A100="X"),(B1:B100-C1:C100))
>
> > Hope this helps.
>
> > Pete
>
> > On Jan 14, 10:08 am, paulkaye <paulmjk...@gmail.com> wrote:
>
> > > Hi,
>
> > > I got a #VALUE! error.
>
> > > I don't quite understand the formula - it looks like it does something
> > > like:
>
> > > * A1*(B1-C1)
> > > +A2*(B2-C2)
> > > +A3*(B3-C3)
> > > ...
>
> > > Could you explain? I was expecting to do something with IF statements!
>
> > > Many thanks again for your time,
>
> > > Paul
>
> > > On Jan 14, 11:37 am, Pete_UK <pashu...@auditel.net> wrote:
>
> > > > Try these:
>
> > > > =SUMPRODUCT((A1:A100="X")*(B1:B100-C1:C100))
>
> > > > =SUMPRODUCT((A1:A100="Y")*(B1:B100-C1:C100))
>
> > > > Adjust column ranges to suit, but you can't have a complete column
> > > > (unless you have XL2007).
>
> > > > Hope this helps.
>
> > > > Pete
>
> > > > On Jan 14, 6:47 am, paulkaye <paulmjk...@gmail.com> wrote:
>
> > > > > Hi,
> > > > > This one is getting the better of me, even though I know I should be
> > > > > able to figure it out myself!:
>
> > > > > I have two columns of data: B and C.
> > > > > I have one column of ID#: A
>
> > > > > I would like to subtract C from B in all rows where ID# is X and
> > > > > display the sum of these values in a single cell. I would then like to
> > > > > do the same for all rows where ID# is Y.
>
> > > > > Please help!
>
> > > > > Many thanks for your time,
>
> > > > > Paul- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

That's great - the whole thing works like a charm!

Thank you!

Paul

On Jan 15, 11:21 am, Pete_UK <pashu...@auditel.net> wrote:
> Hi Paul,
>
> no need for the AND, as the * does the same job - you can re-write it
> as:
>
> =SUMPRODUCT(($C$6:$C$1000="103")*($D$6:$D$1000="120ml")*($I$6:$I
> $1000))
>
> The only other concern I have is whether your product codes are
> numbers or text in column C - you might have to write the first bit
> as:
>
> ($C$6:$C$1000=103)
>
> You can put the values in different cells, eg M1, N1, and then your
> formula becomes:
>
> =SUMPRODUCT(($C$6:$C$1000=M1)*($D$6:$D$1000=N1)*($I$6:$I$1000))
>
> This way you can change the values easily without having to change the
> formula. Also, if the formula is in cell O1 then you can put other
> values in M and N and copy the formula down.
>
> Hope this helps.
>
> Pete
>
> On Jan 15, 8:27 am, paulkaye <paulmjk...@gmail.com> wrote:
>
> > Ah, thank you for that explanation - now I understand what the formula
> > is doing. I obviously simplified the spreadsheet description in my
> > original question but cannot seem to get the correct result. Here is
> > how I have extended your suggestion:
>
> > =SUMPRODUCT((AND($C$6:$C$1000="103",$D$6:$D$1000="120ml")*($I$6:$I
> > $1000)))
>
> > As you can see, there are two conditions. I want to add up the values
> > in column I for product 103 (column C) in size 120ml (column D). I'm
> > getting zero as the result every time. Is there an error in the
> > formula I've entered here? Just for your info, I'm intending to extend
> > the formula further (by using INDIRECT to reference the formula and
> > size names) once I've got this correct.
>
> > Many thanks for your time,
>
> > Paul
>
> > On Jan 14, 12:58 pm, Pete_UK <pashu...@auditel.net> wrote:
>
> > > The first part of the formula (A1:A100="X") is in fact a conditional
> > > statement which will check in turn if A1="X", A2="X", A3="X" etc and
> > > return TRUE or FALSE as appropriate, which will be interpreted as 1 or
> > > 0 respectively. Hence:
>
> > > (1 or 0)*(B1-C1)
> > > +(1 or 0)*(B2-C2)
> > > +(1 or 0)*(B3-C3)
>
> > > will give (Bx - Cx) only where Ax = "X", which is what you want.
>
> > > Check that you have proper numbers in columns B and C, and not text
> > > values. An alternative that you might like to try is:
>
> > > =SUMPRODUCT(--(A1:A100="X"),(B1:B100-C1:C100))
>
> > > Hope this helps.
>
> > > Pete
>
> > > On Jan 14, 10:08 am, paulkaye <paulmjk...@gmail.com> wrote:
>
> > > > Hi,
>
> > > > I got a #VALUE! error.
>
> > > > I don't quite understand the formula - it looks like it does something
> > > > like:
>
> > > > A1*(B1-C1)
> > > > +A2*(B2-C2)
> > > > +A3*(B3-C3)
> > > > ...
>
> > > > Could you explain? I was expecting to do something with IF statements!
>
> > > > Many thanks again for your time,
>
> > > > Paul
>
> > > > On Jan 14, 11:37 am, Pete_UK <pashu...@auditel.net> wrote:
>
> > > > > Try these:
>
> > > > > =SUMPRODUCT((A1:A100="X")*(B1:B100-C1:C100))
>
> > > > > =SUMPRODUCT((A1:A100="Y")*(B1:B100-C1:C100))
>
> > > > > Adjust column ranges to suit, but you can't have a complete column
> > > > > (unless you have XL2007).
>
> > > > > Hope this helps.
>
> > > > > Pete
>
> > > > > On Jan 14, 6:47 am, paulkaye <paulmjk...@gmail.com> wrote:
>
> > > > > > Hi,
> > > > > > This one is getting the better of me, even though I know I should be
> > > > > > able to figure it out myself!:
>
> > > > > > I have two columns of data: B and C.
> > > > > > I have one column of ID#: A
>
> > > > > > I would like to subtract C from B in all rows where ID# is X and
> > > > > > display the sum of these values in a single cell. I would then like to
> > > > > > do the same for all rows where ID# is Y.
>
> > > > > > Please help!
>
> > > > > > Many thanks for your time,
>
> > > > > > Paul- Hide quoted text -
>
> > > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -

You're welcome, Paul - glad it worked for you.

Pete

On Jan 15, 2:51*pm, paulkaye <paulmjk...@gmail.com> wrote:
> That's great - the whole thing works like a charm!
>
> Thank you!
>
> Paul
>
> On Jan 15, 11:21 am, Pete_UK <pashu...@auditel.net> wrote:
>
>
>
> > Hi Paul,
>
> > no need for the AND, as the * does the same job - you can re-write it
> > as:
>
> > =SUMPRODUCT(($C$6:$C$1000="103")*($D$6:$D$1000="120ml")*($I$6:$I
> > $1000))
>
> > The only other concern I have is whether your product codes are
> > numbers or text in column C - you might have to write the first bit
> > as:
>
> > ($C$6:$C$1000=103)
>
> > You can put the values in different cells, eg M1, N1, and then your
> > formula becomes:
>
> > =SUMPRODUCT(($C$6:$C$1000=M1)*($D$6:$D$1000=N1)*($I$6:$I$1000))
>
> > This way you can change the values easily without having to change the
> > formula. Also, if the formula is in cell O1 then you can put other
> > values in M and N and copy the formula down.
>
> > Hope this helps.
>
> > Pete
>
> > On Jan 15, 8:27 am, paulkaye <paulmjk...@gmail.com> wrote:
>
> > > Ah, thank you for that explanation - now I understand what the formula
> > > is doing. I obviously simplified the spreadsheet description in my
> > > original question but cannot seem to get the correct result. Here is
> > > how I have extended your suggestion:
>
> > > =SUMPRODUCT((AND($C$6:$C$1000="103",$D$6:$D$1000="120ml")*($I$6:$I
> > > $1000)))
>
> > > As you can see, there are two conditions. I want to add up the values
> > > in column I for product 103 (column C) in size 120ml (column D). I'm
> > > getting zero as the result every time. Is there an error in the
> > > formula I've entered here? Just for your info, I'm intending to extend
> > > the formula further (by using INDIRECT to reference the formula and
> > > size names) once I've got this correct.
>
> > > Many thanks for your time,
>
> > > Paul
>
> > > On Jan 14, 12:58 pm, Pete_UK <pashu...@auditel.net> wrote:
>
> > > > The first part of the formula (A1:A100="X") is in fact a conditional
> > > > statement which will check in turn if A1="X", A2="X", A3="X" etc and
> > > > return TRUE or FALSE as appropriate, which will be interpreted as 1 or
> > > > 0 respectively. Hence:
>
> > > > *(1 or 0)*(B1-C1)
> > > > +(1 or 0)*(B2-C2)
> > > > +(1 or 0)*(B3-C3)
>
> > > > will give (Bx - Cx) only where Ax = "X", which is what you want.
>
> > > > Check that you have proper numbers in columns B and C, and not text
> > > > values. An alternative that you might like to try is:
>
> > > > =SUMPRODUCT(--(A1:A100="X"),(B1:B100-C1:C100))
>
> > > > Hope this helps.
>
> > > > Pete
>
> > > > On Jan 14, 10:08 am, paulkaye <paulmjk...@gmail.com> wrote:
>
> > > > > Hi,
>
> > > > > I got a #VALUE! error.
>
> > > > > I don't quite understand the formula - it looks like it does something
> > > > > like:
>
> > > > > * A1*(B1-C1)
> > > > > +A2*(B2-C2)
> > > > > +A3*(B3-C3)
> > > > > ...
>
> > > > > Could you explain? I was expecting to do something with IF statements!
>
> > > > > Many thanks again for your time,
>
> > > > > Paul
>
> > > > > On Jan 14, 11:37 am, Pete_UK <pashu...@auditel.net> wrote:
>
> > > > > > Try these:
>
> > > > > > =SUMPRODUCT((A1:A100="X")*(B1:B100-C1:C100))
>
> > > > > > =SUMPRODUCT((A1:A100="Y")*(B1:B100-C1:C100))
>
> > > > > > Adjust column ranges to suit, but you can't have a complete column
> > > > > > (unless you have XL2007).
>
> > > > > > Hope this helps.
>
> > > > > > Pete
>
> > > > > > On Jan 14, 6:47 am, paulkaye <paulmjk...@gmail.com> wrote:
>
> > > > > > > Hi,
> > > > > > > This one is getting the better of me, even though I know I should be
> > > > > > > able to figure it out myself!:
>
> > > > > > > I have two columns of data: B and C.
> > > > > > > I have one column of ID#: A
>
> > > > > > > I would like to subtract C from B in all rows where ID# is X and
> > > > > > > display the sum of these values in a single cell. I would thenlike to
> > > > > > > do the same for all rows where ID# is Y.
>
> > > > > > > Please help!
>
> > > > > > > Many thanks for your time,
>
> > > > > > > Paul- Hide quoted text -
>
> > > > > - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -