Sumif or sumproduct
 

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,

Sumif or sumproduct
 

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

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

HTH,
Elkar


"Klee" wrote:

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,

Sumif or sumproduct
 

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,

"Elkar" wrote:

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

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

HTH,
Elkar


"Klee" wrote:

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,

Sumif or sumproduct
 

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

"Klee" wrote:

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,

"Elkar" wrote:

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

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

HTH,
Elkar


"Klee" wrote:

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,

Sumif or sumproduct
 

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" wrote:

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,

"Elkar" wrote:

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

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

HTH,
Elkar


"Klee" wrote:

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,

Sumif or sumproduct
 

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


"Elkar" wrote:

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" wrote:

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,

"Elkar" wrote:

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

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

HTH,
Elkar


"Klee" wrote:

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,

Sumif or sumproduct
 

Thanks very much!

"Toppers" wrote:

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

"Klee" wrote:

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,

"Elkar" wrote:

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

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

HTH,
Elkar


"Klee" wrote:

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,