Sum with conditions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vicente - 05 Oct 2007 10:22 GMT

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.

Reply to this Message

Barb Reinhardt - 05 Oct 2007 10:56 GMT

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))

Signature

HTH,
Barb Reinhardt

> Hi,
>
[quoted text clipped - 17 lines]
>
> Thanks in advance.

Reply to this Message

Bob Phillips - 05 Oct 2007 11:09 GMT

Try

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

Signature

HTH

Bob

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

> Hi,
>
[quoted text clipped - 17 lines]
>
> Thanks in advance.

Reply to this Message

Barb Reinhardt - 05 Oct 2007 12:59 GMT

Bob, is there something analogous to SUMAR.SI in English?

Signature

HTH,
Barb Reinhardt

> Try
>
[quoted text clipped - 21 lines]
> >
> > Thanks in advance.

Reply to this Message

Bob Phillips - 05 Oct 2007 13:06 GMT

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 :-)

Signature

HTH

Bob

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

> Bob, is there something analogous to SUMAR.SI in English?
>
[quoted text clipped - 23 lines]
>> >
>> > Thanks in advance.

Reply to this Message

Dave Peterson - 05 Oct 2007 13:09 GMT

The syntax makes it look like =sumif().

> Bob, is there something analogous to SUMAR.SI in English?
> --
[quoted text clipped - 33 lines]
> > >
> > > Thanks in advance.

Signature

Dave Peterson

Reply to this Message

Bob Phillips - 05 Oct 2007 13:27 GMT

Come on, surely a good Swede speaks Spanish :-)

Signature

HTH

Bob

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

> The syntax makes it look like =sumif().
>
[quoted text clipped - 36 lines]
>> > >
>> > > Thanks in advance.

Reply to this Message

Dave Peterson - 05 Oct 2007 14:02 GMT

We'll have to check with Peo!

> Come on, surely a good Swede speaks Spanish :-)
>
[quoted text clipped - 49 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Rate this thread: