Sumif or sumproduct

K

Klee

Hi there,

I am trying to do a sumif using two criteria. I thought I might be able to
combine a Sumif with And: but that didn't work.

I'm trying to sum m4:m80 but only cells where both they and the cell beside
them in N4:n80 are both <0.

Based on what I have been reading here I have come up with this:

=SUMPRODUCT(--(M4:M80 "<0"),--(n4:n80 "<0"),m4:m80))

I've never used sumproduct or -- but it doesn't seem to work. What am i
doing wrong?

Thanks,
 
E

Elkar

You're almost there, just take out those quotes.

=SUMPRODUCT(--(M4:M80<0),--(n4:n80<0),m4:m80))

HTH,
Elkar
 
K

Klee

Hi Elkar,

Thanks very much for your help. The good news is that your formula works
perfectly. The bad news is that it made me realize that it isn't the formula
that I need (duh!).
I think the first half is right but is there a way to make the second
criteria "only if the cell beside it in N4:N80 is the same value"? I tried
this:

=SUMPRODUCT(--(M4:M80<0),--(M4:M80=N4:N80),M4:M80). but it didn't work.
Like if M3 and N3 are both -5 then I dont want them included in the sum but
if M3=-5 and N3="" or 0 or anything else then I do want them included.

Thanks again,
 
E

Elkar

Once again, I think you're almost there. You seem to be contradicting
yourself as to whether Column M and N should be equal to each other or not.
Depending which it is, one of these formulas should work:

=SUMPRODUCT(--(M4:M80<0),--(N4:N80<>M4:M80),M4:M80)

=SUMPRODUCT(--(M4:M80<0),--(N4:N80=M4:M80),M4:M80)

HTH,
Elkar
 
K

Klee

Sorry about the confusion. The first one worked perfectly. Thanks so much for
the help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top