Sum with conditions

V

Vicente

Hi,

I've read a lot of posts in this group about this issue, but I can't
find a solution.

I am using this array formula:

{=SUMA(((ConsultaPosiciones!E2:E100)=B1)*(ConsultaPosiciones!J2:J100);
0)}

But I get "#VALUE!# (translated from spanish).

I found that the problem is with som empty cells in column "J", when
I set the values of those cells the function works.

The problem is that I cannot set those values due the huge amount of
data.

Is ther any way to ignore those empty cells?

Thanks in advance.
 
B

Barb Reinhardt

I don't have SUMA in Excel 2003. I've always done this type of thing using
SUMPRODUCT.

=SUMA(--(ConsultaPosiciones!E2:E100=B1),(ConsultaPosiciones!J2:J100))
 
B

Bob Phillips

Try

=SUMAR.SI(ConsultaPosiciones!E2:E100;B1;ConsultaPosiciones!J2:J100)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
B

Bob Phillips

That is SUMIF Barb. I used that rather than SUMPRODUCT because Vicente only
had a single condition.His formula should have worked, but it is an array
formula, and we all know the problems with them, hence the profligate use of
our beloved SUMPRODUCT :)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
B

Bob Phillips

Come on, surely a good Swede speaks Spanish :)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
D

Dave Peterson

We'll have to check with Peo!


Bob said:
Come on, surely a good Swede speaks Spanish :)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...
 
Top