Sumif or sumproduct

[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,

 

[Elkar]

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

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

HTH,
Elkar

 

[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,

 

[Toppers]

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

 

[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

 

[Klee]

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

 

[Klee]

Thanks very much!

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