SUMIF function retrieves "0"

E

Easydoesit

This is a re-send due to no reply before.

I am getting a 0 as my result, consistently. I have three columns. If
$A$n=$A$(n-1), I want to add the value in $B$(n-1) to $B$n. I incremented
the SUMIF value by extending the formula from C2 all the way down.

=SUMIF(b2:b3,A3=A2,b2:b3)

What might keep the function from working properly?
--Dave.
 
J

JMB

The first argument for sumif is the criteria range, second argument is the
criteria (sumif implicitly compares the values in the criteria range against
this criteria, so it is usually expressed as "=5000", "<5000", or could be a
cell reference), third argument is the range to be summed. At any rate, it
doesn't sound like what you need

Try

=SUMPRODUCT((A2=A3)*(B2+B3))

You could use an If statement to return "" if A2<>A3.

=IF(A2<>A3,"",SUMPRODUCT((A2=A3)*(B2+B3))
 
J

JMB

Actually with an IF statement, you don't even need SUMPRODUCT (been a long
day).

=IF(A2<>A3,"",B2+B3)
 
R

RagDyeR

I'm curious.

Have you tried JMB's formula?

What do you want to happen when A2 and A3 don't match?
 
J

JMB

you just have to switch the order

=IF(A2=A3,,B2+B3,"")

IF(test, condition if true, condition if false)

Do any of these formulas take you in the direction you want to go?
 
E

Easydoesit

Sure, the IF function works well enough. I wanted to simplify using SUMIF
since it seemed to be exactly what I want to do. But it must have some
limitation that I haven't figured out.

If A(n) not equal A(n-1), nothing happens in the corresponding C cell, and
that row becomes the first of the next sequence. The blank in the C cell
will help me find the discontinuances.
 
J

JMB

Need to point out I had too many commas in the last post. Should have read:

=IF(A2=A3,B2+B3,"")

You could substitute "" with whatever else you want to do with the
discontinuances. for example:

=IF(A2=A3,B2+B3,"No Match")

or return a zero or perform a different calculation.
 
Top