Adding items in a column if value in a second column is X.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

paulkaye - 14 Jan 2008 06:47 GMT

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

Reply to this Message

Pete_UK - 14 Jan 2008 09:37 GMT

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

> Hi,
> This one is getting the better of me, even though I know I should be
[quoted text clipped - 12 lines]
>
> Paul

Reply to this Message

paulkaye - 14 Jan 2008 10:08 GMT

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

> Try these:
>
[quoted text clipped - 25 lines]
>
> > Paul

Reply to this Message

Pete_UK - 14 Jan 2008 10:58 GMT

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

> Hi,
>
[quoted text clipped - 45 lines]
>
> - Show quoted text -

Reply to this Message

paulkaye - 15 Jan 2008 08:27 GMT

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

> 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
[quoted text clipped - 65 lines]
>
> > - Show quoted text -

Reply to this Message

Pete_UK - 15 Jan 2008 09:21 GMT

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

> Ah, thank you for that explanation - now I understand what the formula
> is doing. I obviously simplified the spreadsheet description in my
[quoted text clipped - 86 lines]
>
> - Show quoted text -

Reply to this Message

paulkaye - 15 Jan 2008 14:51 GMT

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

Thank you!

Paul

> Hi Paul,
>
[quoted text clipped - 113 lines]
>
> > - Show quoted text -

Reply to this Message

Pete_UK - 15 Jan 2008 15:06 GMT

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

Pete

> That's great - the whole thing works like a charm!
>
[quoted text clipped - 121 lines]
>
> - Show quoted text -

Reply to this Message