If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Can I SUM column C IF Column A=X AND Column D=Y?
If you could follow that...
I have a number of columns of information for any given row. I would like to set up a sheet that will sum Column C for all rows IF Column A = {a given value} AND Column D = {a different given value}. I may even need to throw in that Column B = {another given value}. Can I do this and how? I've run across something about VLOOKUP, but I'm not sure how to make that work. I'm primarily a CAD operator making brief forays into Excel, and I keep thinking it has got to be easier than all the manual sorting I'm currently doing. I'm using Excel 2003 (on Windows XP SP2) |
#2
|
|||
|
|||
Can I SUM column C IF Column A=X AND Column D=Y?
Assign your "given values" to particular cells, so they can easily be
changed, without having to revise the formula itself (E1, E2). =Sumproduct((A1:A100=E1)*(D1100=E2)*C1:C100) To add other criteria, simply add another argument: =Sumproduct((A1:A100=E1)*(D1100=E2)*(B1:B100=E3) *C1:C100) All ranges should be the same size, and you *cannot* use entire columns (A:A - B:B) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Minx" wrote in message ... If you could follow that... I have a number of columns of information for any given row. I would like to set up a sheet that will sum Column C for all rows IF Column A = {a given value} AND Column D = {a different given value}. I may even need to throw in that Column B = {another given value}. Can I do this and how? I've run across something about VLOOKUP, but I'm not sure how to make that work. I'm primarily a CAD operator making brief forays into Excel, and I keep thinking it has got to be easier than all the manual sorting I'm currently doing. I'm using Excel 2003 (on Windows XP SP2) |
#3
|
|||
|
|||
Can I SUM column C IF Column A=X AND Column D=Y?
=SUMPRODUCT((Xewks!A1:A425=A10)*(Xewks!E1:E425=5)* (Xewks!D1425="B
Borrow")*Xewks!B1:B425) Here is what I put in for my SUMPRODUCT formula, but it just gives me a #VALUE error. Closer than what I was doing before, but still not working - so I must be misunderstanding something. Thank you for your help! "RagDyeR" wrote: Assign your "given values" to particular cells, so they can easily be changed, without having to revise the formula itself (E1, E2). =Sumproduct((A1:A100=E1)*(D1100=E2)*C1:C100) To add other criteria, simply add another argument: =Sumproduct((A1:A100=E1)*(D1100=E2)*(B1:B100=E3) *C1:C100) All ranges should be the same size, and you *cannot* use entire columns (A:A - B:B) |
#4
|
|||
|
|||
Can I SUM column C IF Column A=X AND Column D=Y?
You probably have values in B1 to B425 that are *not* true XL recognized
numbers! Could B1 be a text header? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Minx" wrote in message ... =SUMPRODUCT((Xewks!A1:A425=A10)*(Xewks!E1:E425=5)* (Xewks!D1425="B Borrow")*Xewks!B1:B425) Here is what I put in for my SUMPRODUCT formula, but it just gives me a #VALUE error. Closer than what I was doing before, but still not working - so I must be misunderstanding something. Thank you for your help! "RagDyeR" wrote: Assign your "given values" to particular cells, so they can easily be changed, without having to revise the formula itself (E1, E2). =Sumproduct((A1:A100=E1)*(D1100=E2)*C1:C100) To add other criteria, simply add another argument: =Sumproduct((A1:A100=E1)*(D1100=E2)*(B1:B100=E3) *C1:C100) All ranges should be the same size, and you *cannot* use entire columns (A:A - B:B) |
#5
|
|||
|
|||
Can I SUM column C IF Column A=X AND Column D=Y?
That was it!
(Actually, everything in Row 1 is a text header) Thank you so much!! "RagDyeR" wrote: You probably have values in B1 to B425 that are *not* true XL recognized numbers! Could B1 be a text header? |
#6
|
|||
|
|||
Can I SUM column C IF Column A=X AND Column D=Y?
You're welcome, and thank you for the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Minx" wrote in message ... That was it! (Actually, everything in Row 1 is a text header) Thank you so much!! "RagDyeR" wrote: You probably have values in B1 to B425 that are *not* true XL recognized numbers! Could B1 be a text header? |
#7
|
|||
|
|||
Can I SUM column C IF Column A=X AND Column D=Y?
I am trying to sum a column of both negative and positive numbers on another
worksheet based on two conditions. I have tried to construct an array that would =Sum(if((A2:A278,="2000")*(M2:M278,="0"),M2:M278) ) Should that not sum the column M2:M278 positive integers based on the criteria that all rows in A2:A278 are equal to "2000".??? "Minx" wrote: If you could follow that... I have a number of columns of information for any given row. I would like to set up a sheet that will sum Column C for all rows IF Column A = {a given value} AND Column D = {a different given value}. I may even need to throw in that Column B = {another given value}. Can I do this and how? I've run across something about VLOOKUP, but I'm not sure how to make that work. I'm primarily a CAD operator making brief forays into Excel, and I keep thinking it has got to be easier than all the manual sorting I'm currently doing. I'm using Excel 2003 (on Windows XP SP2) |
#8
|
|||
|
|||
Can I SUM column C IF Column A=X AND Column D=Y?
To exclude negative values from the sum...
Works in any "modern" version of Excel: =SUMPRODUCT(--(A2:A278=2000),--(M2:M2780),M2:M278) If you're using Excel 2007: =SUMIFS(M2:M278,A2:A278,2000,M2:M278,"0") -- Biff Microsoft Excel MVP "Terry" wrote in message ... I am trying to sum a column of both negative and positive numbers on another worksheet based on two conditions. I have tried to construct an array that would =Sum(if((A2:A278,="2000")*(M2:M278,="0"),M2:M278) ) Should that not sum the column M2:M278 positive integers based on the criteria that all rows in A2:A278 are equal to "2000".??? "Minx" wrote: If you could follow that... I have a number of columns of information for any given row. I would like to set up a sheet that will sum Column C for all rows IF Column A = {a given value} AND Column D = {a different given value}. I may even need to throw in that Column B = {another given value}. Can I do this and how? I've run across something about VLOOKUP, but I'm not sure how to make that work. I'm primarily a CAD operator making brief forays into Excel, and I keep thinking it has got to be easier than all the manual sorting I'm currently doing. I'm using Excel 2003 (on Windows XP SP2) |
#9
|
|||
|
|||
Can I SUM column C IF Column A=X AND Column D=Y?
=SUM(IF((A2:A278=2000)*(M2:M278=0),M2:M278))
This is an array formula CTRL + SHIFT + ENTER Gord Dibben MS Excel MVP On Sun, 25 Apr 2010 10:37:01 -0700, Terry wrote: I am trying to sum a column of both negative and positive numbers on another worksheet based on two conditions. I have tried to construct an array that would =Sum(if((A2:A278,="2000")*(M2:M278,="0"),M2:M278) ) Should that not sum the column M2:M278 positive integers based on the criteria that all rows in A2:A278 are equal to "2000".??? "Minx" wrote: If you could follow that... I have a number of columns of information for any given row. I would like to set up a sheet that will sum Column C for all rows IF Column A = {a given value} AND Column D = {a different given value}. I may even need to throw in that Column B = {another given value}. Can I do this and how? I've run across something about VLOOKUP, but I'm not sure how to make that work. I'm primarily a CAD operator making brief forays into Excel, and I keep thinking it has got to be easier than all the manual sorting I'm currently doing. I'm using Excel 2003 (on Windows XP SP2) |
Thread Tools | |
Display Modes | |
|
|