Can I SUM column C IF Column A=X AND Column D=Y? in Excel Worksheet Functions    |   |  Help   Search For:     In:   Office   Excel     Excel General Questions     Excel New users     Excel Application Errors     Excel Charts     Excel Programming     Excel Setup     Excel Worksheet Functions              Minx 12/13/2006 8:03 AM PST   Question   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)     Was this post helpful to you?         Reply |   Top           RagDyeR 12/13/2006 8:17 AM PST   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)*(D1:D100=E2)*C1:C100) To add other criteria, simply add another argument: =Sumproduct((A1:A100=E1)*(D1:D100=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" <Minx@discussions.microsoft.com> wrote in message news:E10F52FE-9F8E-4B3B-9EC4-7545D608EBA7@microsoft.com... 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)     Was this post helpful to you?         Reply |   Top           Minx 12/13/2006 8:41 AM PST   =SUMPRODUCT((Xewks!A1:A425=A10)*(Xewks!E1:E425=5)*(Xewks!D1:D425="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)*(D1:D100=E2)*C1:C100) > > To add other criteria, simply add another argument: > > =Sumproduct((A1:A100=E1)*(D1:D100=E2)*(B1:B100=E3)*C1:C100) > > All ranges should be the same size, and you *cannot* use entire columns > (A:A - B:B)     Was this post helpful to you?         Reply |   Top           RagDyeR 12/13/2006 9:11 AM PST   Answer   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" <Minx@discussions.microsoft.com> wrote in message news:0CEC83B2-CB59-471A-A101-4BAD6E3400FB@microsoft.com... =SUMPRODUCT((Xewks!A1:A425=A10)*(Xewks!E1:E425=5)*(Xewks!D1:D425="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)*(D1:D100=E2)*C1:C100) > > To add other criteria, simply add another argument: > > =Sumproduct((A1:A100=E1)*(D1:D100=E2)*(B1:B100=E3)*C1:C100) > > All ranges should be the same size, and you *cannot* use entire columns > (A:A - B:B)     Was this post helpful to you?         Reply |   Top           Minx 12/13/2006 9:32 AM PST   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?     Was this post helpful to you?         Reply |   Top           RagDyer 12/13/2006 12:17 PM PST   You're welcome, and thank you for the feed-back. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Minx" <Minx@discussions.microsoft.com> wrote in message news:5AB9D85E-BF38-453B-9673-25376BD00B6F@microsoft.com... > 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? >     Was this post helpful to you?         Reply |   Top           Seb 10/8/2009 9:30 PM PST   Hi, This formula works great whenever there are values in the columns, but when the columns contain formulas I get #N/A. Do you know how to go about this? Seb "RagDyer" wrote: > You're welcome, and thank you for the feed-back. > -- > Regards, > > RD > > --------------------------------------------------------------------------- > Please keep all correspondence within the NewsGroup, so all may benefit ! > --------------------------------------------------------------------------- > "Minx" <Minx@discussions.microsoft.com> wrote in message > news:5AB9D85E-BF38-453B-9673-25376BD00B6F@microsoft.com... > > 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? > > > >     Was this post helpful to you?         Reply |   Top           T. Valko 10/8/2009 9:44 PM PST   >when the columns contain formulas I get #N/A. Post the *exact* formula you are trying to use that returns #N/A. -- Biff Microsoft Excel MVP "Seb" <Seb@discussions.microsoft.com> wrote in message news:4A761E9C-FDD1-4F6D-B8D4-7A984693811B@microsoft.com... > Hi, > > This formula works great whenever there are values in the columns, but > when > the columns contain formulas I get #N/A. Do you know how to go about this? > > Seb > > "RagDyer" wrote: > >> You're welcome, and thank you for the feed-back. >> -- >> Regards, >> >> RD >> >> --------------------------------------------------------------------------- >> Please keep all correspondence within the NewsGroup, so all may benefit ! >> --------------------------------------------------------------------------- >> "Minx" <Minx@discussions.microsoft.com> wrote in message >> news:5AB9D85E-BF38-453B-9673-25376BD00B6F@microsoft.com... >> > 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? >> > >> >>     Was this post helpful to you?         Reply |   Top           Seb 10/8/2009 10:31 PM PST   This is the formula that returns #N/A: =SUM(IF(('New Code'!$AG$2:$AG$7176=Input!$B$31)*('New Code'!$AI$2:$AI$7176=B10),'New Code'!$AM$2:$AM$7176)) and I enter it as an array formula This one, for example, works: =SUM(IF(('New Code'!$AG$2:$AG$7176=Input!$B$31)*('New Code'!$AF$2:$AF$7176=F7),'New Code'!$AM$2:$AM$7176)) also entered as an array formula Seb "T. Valko" wrote: > >when the columns contain formulas I get #N/A. > > Post the *exact* formula you are trying to use that returns #N/A. > > -- > Biff > Microsoft Excel MVP > > > "Seb" <Seb@discussions.microsoft.com> wrote in message > news:4A761E9C-FDD1-4F6D-B8D4-7A984693811B@microsoft.com... > > Hi, > > > > This formula works great whenever there are values in the columns, but > > when > > the columns contain formulas I get #N/A. Do you know how to go about this? > > > > Seb > > > > "RagDyer" wrote: > > > >> You're welcome, and thank you for the feed-back. > >> -- > >> Regards, > >> > >> RD > >> > >> --------------------------------------------------------------------------- > >> Please keep all correspondence within the NewsGroup, so all may benefit ! > >> --------------------------------------------------------------------------- > >> "Minx" <Minx@discussions.microsoft.com> wrote in message > >> news:5AB9D85E-BF38-453B-9673-25376BD00B6F@microsoft.com... > >> > 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? > >> > > >> > >> > > >     Was this post helpful to you?         Reply |   Top           T. Valko 10/8/2009 11:01 PM PST   The only thing that's different in those formulas other than the criteria cells is the range AI2:AI7176. Are there any #N/A errors already in that range? If so, can you fix the formulas so they don't return those errors? If you can't or don't want to fix those, what type of data is in that range, text, numbers, both? -- Biff Microsoft Excel MVP "Seb" <Seb@discussions.microsoft.com> wrote in message news:4A874A78-8996-4A39-8FDF-F85A51F99A3E@microsoft.com... > This is the formula that returns #N/A: > =SUM(IF(('New Code'!$AG$2:$AG$7176=Input!$B$31)*('New > Code'!$AI$2:$AI$7176=B10),'New Code'!$AM$2:$AM$7176)) and I enter it as an > array formula > > This one, for example, works: > =SUM(IF(('New Code'!$AG$2:$AG$7176=Input!$B$31)*('New > Code'!$AF$2:$AF$7176=F7),'New Code'!$AM$2:$AM$7176)) also entered as an > array > formula > > Seb > > > > "T. Valko" wrote: > >> >when the columns contain formulas I get #N/A. >> >> Post the *exact* formula you are trying to use that returns #N/A. >> >> -- >> Biff >> Microsoft Excel MVP >> >> >> "Seb" <Seb@discussions.microsoft.com> wrote in message >> news:4A761E9C-FDD1-4F6D-B8D4-7A984693811B@microsoft.com... >> > Hi, >> > >> > This formula works great whenever there are values in the columns, but >> > when >> > the columns contain formulas I get #N/A. Do you know how to go about >> > this? >> > >> > Seb >> > >> > "RagDyer" wrote: >> > >> >> You're welcome, and thank you for the feed-back. >> >> -- >> >> Regards, >> >> >> >> RD >> >> >> >> --------------------------------------------------------------------------- >> >> Please keep all correspondence within the NewsGroup, so all may >> >> benefit ! >> >> --------------------------------------------------------------------------- >> >> "Minx" <Minx@discussions.microsoft.com> wrote in message >> >> news:5AB9D85E-BF38-453B-9673-25376BD00B6F@microsoft.com... >> >> > 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? >> >> > >> >> >> >> >> >> >>     Was this post helpful to you?         Reply |   Top           Seb 10/8/2009 11:29 PM PST   Thank you so much. I did have one #N/A in the range. "T. Valko" wrote: > The only thing that's different in those formulas other than the criteria > cells is the range AI2:AI7176. > > Are there any #N/A errors already in that range? If so, can you fix the > formulas so they don't return those errors? If you can't or don't want to > fix those, what type of data is in that range, text, numbers, both? > > -- > Biff > Microsoft Excel MVP > > > "Seb" <Seb@discussions.microsoft.com> wrote in message > news:4A874A78-8996-4A39-8FDF-F85A51F99A3E@microsoft.com... > > This is the formula that returns #N/A: > > =SUM(IF(('New Code'!$AG$2:$AG$7176=Input!$B$31)*('New > > Code'!$AI$2:$AI$7176=B10),'New Code'!$AM$2:$AM$7176)) and I enter it as an > > array formula > > > > This one, for example, works: > > =SUM(IF(('New Code'!$AG$2:$AG$7176=Input!$B$31)*('New > > Code'!$AF$2:$AF$7176=F7),'New Code'!$AM$2:$AM$7176)) also entered as an > > array > > formula > > > > Seb > > > > > > > > "T. Valko" wrote: > > > >> >when the columns contain formulas I get #N/A. > >> > >> Post the *exact* formula you are trying to use that returns #N/A. > >> > >> -- > >> Biff > >> Microsoft Excel MVP > >> > >> > >> "Seb" <Seb@discussions.microsoft.com> wrote in message > >> news:4A761E9C-FDD1-4F6D-B8D4-7A984693811B@microsoft.com... > >> > Hi, > >> > > >> > This formula works great whenever there are values in the columns, but > >> > when > >> > the columns contain formulas I get #N/A. Do you know how to go about > >> > this? > >> > > >> > Seb > >> > > >> > "RagDyer" wrote: > >> > > >> >> You're welcome, and thank you for the feed-back. > >> >> -- > >> >> Regards, > >> >> > >> >> RD > >> >> > >> >> --------------------------------------------------------------------------- > >> >> Please keep all correspondence within the NewsGroup, so all may > >> >> benefit ! > >> >> --------------------------------------------------------------------------- > >> >> "Minx" <Minx@discussions.microsoft.com> wrote in message > >> >> news:5AB9D85E-BF38-453B-9673-25376BD00B6F@microsoft.com... > >> >> > 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? > >> >> > > >> >> > >> >> > >> > >> > >> > > >     Was this post helpful to you?         Reply |   Top           T. Valko 10/9/2009 8:25 AM PST   Good deal. Thanks for the feedback! -- Biff Microsoft Excel MVP "Seb" <Seb@discussions.microsoft.com> wrote in message news:ABC23115-DE8E-49D8-ACCD-57E152F61A0C@microsoft.com... > Thank you so much. I did have one #N/A in the range. > > "T. Valko" wrote: > >> The only thing that's different in those formulas other than the criteria >> cells is the range AI2:AI7176. >> >> Are there any #N/A errors already in that range? If so, can you fix the >> formulas so they don't return those errors? If you can't or don't want to >> fix those, what type of data is in that range, text, numbers, both? >> >> -- >> Biff >> Microsoft Excel MVP >> >> >> "Seb" <Seb@discussions.microsoft.com> wrote in message >> news:4A874A78-8996-4A39-8FDF-F85A51F99A3E@microsoft.com... >> > This is the formula that returns #N/A: >> > =SUM(IF(('New Code'!$AG$2:$AG$7176=Input!$B$31)*('New >> > Code'!$AI$2:$AI$7176=B10),'New Code'!$AM$2:$AM$7176)) and I enter it as >> > an >> > array formula >> > >> > This one, for example, works: >> > =SUM(IF(('New Code'!$AG$2:$AG$7176=Input!$B$31)*('New >> > Code'!$AF$2:$AF$7176=F7),'New Code'!$AM$2:$AM$7176)) also entered as an >> > array >> > formula >> > >> > Seb >> > >> > >> > >> > "T. Valko" wrote: >> > >> >> >when the columns contain formulas I get #N/A. >> >> >> >> Post the *exact* formula you are trying to use that returns #N/A. >> >> >> >> -- >> >> Biff >> >> Microsoft Excel MVP >> >> >> >> >> >> "Seb" <Seb@discussions.microsoft.com> wrote in message >> >> news:4A761E9C-FDD1-4F6D-B8D4-7A984693811B@microsoft.com... >> >> > Hi, >> >> > >> >> > This formula works great whenever there are values in the columns, >> >> > but >> >> > when >> >> > the columns contain formulas I get #N/A. Do you know how to go about >> >> > this? >> >> > >> >> > Seb >> >> > >> >> > "RagDyer" wrote: >> >> > >> >> >> You're welcome, and thank you for the feed-back. >> >> >> -- >> >> >> Regards, >> >> >> >> >> >> RD >> >> >> >> >> >> --------------------------------------------------------------------------- >> >> >> Please keep all correspondence within the NewsGroup, so all may >> >> >> benefit ! >> >> >> --------------------------------------------------------------------------- >> >> >> "Minx" <Minx@discussions.microsoft.com> wrote in message >> >> >> news:5AB9D85E-BF38-453B-9673-25376BD00B6F@microsoft.com... >> >> >> > 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? >> >> >> > >> >> >> >> >> >> >> >> >> >> >> >> >> >> >>     Was this post helpful to you?         Reply |   Top           travcoe21 11/7/2009 9:39 AM PST   Hi, Biff -- I just stumbled across this thread, and made use of the formulation for conditional summing with complex criteria. Thanks a bunch! Question for you: the formula looks like, and behaves like, there is an implied conversion from Boolean values of TRUE/FALSE to numerical values of 1/0 going on. That is, if all conditions are true, multiply the sum column value by 1. If not all of the conditions are true, multiply the sum column value by 0. IOW, a logical AND of multiple conditions. Is that a good description? I tried what I thought was the equivalent: {SUM(AND(cond1, cond2)*sumcolumnvalue)} . . . but that didn't work, I assume because of the logic of the array formula syntax -- correct? I also discovered a further subtlety of this formulation. What I actually needed to do logically was: {SUM(OR(AND(cond1, cond2),AND(cond1, cond3))*sumcolumnvalue)} The syntax that gave the correct result was to add two separate conditional summations together, i.e.: {SUM(cond1*cond2*sumcolumnvalue)+SUM(cond1*cond3*sumcolumnvalue)} Note that in order for this to work the way it's supposed to, cond1 and cond3 must never both be true for the same sumcolumnvalue. Which for my application is always the case. Anywho, just wanted to share this discovery and help out anyone else who is trying to do something similar. "T. Valko" wrote: > The only thing that's different in those formulas other than the criteria > cells is the range AI2:AI7176. > > Are there any #N/A errors already in that range? If so, can you fix the > formulas so they don't return those errors? If you can't or don't want to > fix those, what type of data is in that range, text, numbers, both? > > -- > Biff > Microsoft Excel MVP > > > "Seb" <Seb@discussions.microsoft.com> wrote in message > news:4A874A78-8996-4A39-8FDF-F85A51F99A3E@microsoft.com... > > This is the formula that returns #N/A: > > =SUM(IF(('New Code'!$AG$2:$AG$7176=Input!$B$31)*('New > > Code'!$AI$2:$AI$7176=B10),'New Code'!$AM$2:$AM$7176)) and I enter it as an > > array formula > > > > This one, for example, works: > > =SUM(IF(('New Code'!$AG$2:$AG$7176=Input!$B$31)*('New > > Code'!$AF$2:$AF$7176=F7),'New Code'!$AM$2:$AM$7176)) also entered as an > > array > > formula > > > > Seb > > > > > > > > "T. Valko" wrote: > > > >> >when the columns contain formulas I get #N/A. > >> > >> Post the *exact* formula you are trying to use that returns #N/A. > >> > >> -- > >> Biff > >> Microsoft Excel MVP > >> > >> > >> "Seb" <Seb@discussions.microsoft.com> wrote in message > >> news:4A761E9C-FDD1-4F6D-B8D4-7A984693811B@microsoft.com... > >> > Hi, > >> > > >> > This formula works great whenever there are values in the columns, but > >> > when > >> > the columns contain formulas I get #N/A. Do you know how to go about > >> > this? > >> > > >> > Seb > >> > > >> > "RagDyer" wrote: > >> > > >> >> You're welcome, and thank you for the feed-back. > >> >> -- > >> >> Regards, > >> >> > >> >> RD > >> >> > >> >> --------------------------------------------------------------------------- > >> >> Please keep all correspondence within the NewsGroup, so all may > >> >> benefit ! > >> >> --------------------------------------------------------------------------- > >> >> "Minx" <Minx@discussions.microsoft.com> wrote in message > >> >> news:5AB9D85E-BF38-453B-9673-25376BD00B6F@microsoft.com... > >> >> > 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? > >> >> > > >> >> > >> >> > >> > >> > >> > > >     Was this post helpful to you?         Reply |   Top           travcoe21 11/7/2009 9:50 AM PST   Sorry, I meant cond2 and cond3 are never both true for the same sumcolumnvalue. Jeff "travcoe21" wrote: > Note that in order for this to work the way it's supposed to, cond1 and > cond3 must never both be true for the same sumcolumnvalue. Which for my > application is always the case.     Was this post helpful to you?         Reply |   Top           T. Valko 11/10/2009 4:03 PM PST   >Is that a good description? Yes >{SUM(AND(cond1, cond2)*sumcolumnvalue)} AND returns a single result where you need an array of results. It sounds like you want an OR comparison: sum C if A = x *or* D = y. Try one of these: =SUMPRODUCT(--((A1:A10="x")+(D1:D10="y")>0),C1:C10) =SUMPRODUCT(SIGN((A1:A10="x")+(D1:D10="y")),C1:C10) -- Biff Microsoft Excel MVP "travcoe21" <travcoe21@discussions.microsoft.com> wrote in message news:11A68799-026D-49AF-A5A3-315D770EAD56@microsoft.com... > Sorry, I meant cond2 and cond3 are never both true for the same > sumcolumnvalue. > > Jeff > > "travcoe21" wrote: >> Note that in order for this to work the way it's supposed to, cond1 and >> cond3 must never both be true for the same sumcolumnvalue. Which for my >> application is always the case. .     Was this post helpful to you?         Reply |   Top         Return to Microsoft Communities   Notify me of replies