Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
HELP !!! SUMIF or SUMPRODUCT | Excel Discussion (Misc queries) | |||
HELP Sumif or Sumproduct | Excel Discussion (Misc queries) | |||
SUMIF,SUMPRODUCT | New Users to Excel | |||
sumif or sumproduct? | Excel Worksheet Functions |