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

  • Thread starter Thread starter paulkaye
  • Start date Start date
P

paulkaye

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
 
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
 
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
 
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
 
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
 
Back
Top