Problems with sum + if

[Guest]

Hi,

I have been trying to sum from a column after it meets three conditions. I
have tried three things now and I can't get any of them to work.

The first thing I tried was to take it through a conditional sum wizard, and
my formula looks like this:

=SUM(IF($A$3:$A$55="S",IF($C$3:$C$55=-12.5,IF($D$3:$D$55="SEP06MAR07",$B$3:$B$55,0),0),0))

What I would like for it to do is add the value from column c only when the
same row value in column b is "S", when column d is "SEP06MAR07", and when
column C is -12.5, but for some reason it's not working.

Then I tried to make it myself with:

=SUM(IF(($A$3:$A$55=I$2)*($C$3:$C$55=$G4)*($D$3:$D$55=$G$2),$B$3:$B$55,0))

where I$2 = S, $G4 = -12.5, and $G$2 = SEP06MAR07.
I think that because the first row in the database satisfies these
conditions, it is summing up the entire column b.

I have also tried to use the DSUM method:

=DSUM(A255,"QUAN",G6:I7)

Where "QUAN" serves as the title for the column I would like to be summed
and G6:17 looks like:

B/S PRICE TYPE
S -12.5 =SEP06MAR07

I have tried all 3 and I can't seem to get any of them to work. Any help you
can give me?

Thanks,
Darren

 

[ph8]

I'm not sure I fully understood what you needed. More particularly I a
unsure of what you wanted actually summed. This formula below wil
perform the 'sum' operation when B2=S, D2=SEP06MAR07, and C2=-12.5.
You will want to edit the actual SUM function though, because like
said, I am not sure what you wanted added together.

=if(B2="S",if(D2="SEP06MAR07",if(C2=-12.5,sum(B2:C3),""),""),"")

Is that anything close to what you wanted done

 

[Guest]

Actually, what I'd like to do is sum all the cells down column b whose
corresponding rows satisfy the three conditions described in the formulas I
have been trying to use.

Basically, column b has quantities of a certain product ("SEP06MAR07"
condition) bought or sold ("S" condition) at a certain price (-12.5
condition) and that information is contained in the same row as the cell I
want to evaluate to be summed all the way down the database. So, something
like this:

B/S QUAN PRICE TYPE
S 3 -12.5 SEP06MAR07
S 3 -12.5 SEP06MAR07
S 2 -7.5 DEC06MAR07
S 2 -7.5 DEC06MAR07
S 3 -7.5 DEC06MAR07
S 7 -7.5 DEC06MAR07
S 3 -7.5 DEC06MAR07
S 2 0.5 DEC07MAR08
S 3 0.5 DEC07MAR08
S 2 0.5 DEC07MAR08
S 2 -5 SEP06DEC06
S 2 -7.5 DEC06MAR07
B 10 -13 SEP06MAR07
B 7 0.5 SEP07MAR08
S 14 -12.5 SEP06MAR07

What I would like to do is add the cell value in column QUAN whenever the
conditions are met in the corresponding row all the way down the column. So,
for the particular example, I would hope to have the cell that the formula is
in to return the value 20 based on the required conditions.

 

[Mikeopolo]

With your matrix pasted in to cells A1 thru D16, and each of the column
given a range name (rows 2 to 16 only), then in any cell type:

=SUMPRODUCT((BS="S")*(PRICE=-12.5)*(TYPE="SEP06MAR07")*QUAN)

which should give an answer of 20.

Regards
Mik