Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I select on two variables in a range of data in excel
I have a large range of data that I am working with and I would like to be
able to sum a colum based on two variables I have been trying to use sumif and dsum but don't really have the answer I am looking for, any suggestions? -- Jeff |
#2
|
|||
|
|||
Hi!
Do you mean that you have 2 variables in the same column? If so, try one of these: =SUMIF(A1:A10,variable_1,B1:B10)+SUMIF(A1:A10,vari able_2,B1:B10) =SUMPRODUCT(--(A1:A10=variable_1)+(A1:A10=variable_2),B1:B10) If the variables are in different columns: =SUMPRODUCT(--(A1:A10=variable_1),--(B1:B10=variable_2),C1:C10) In all of the above, if the variables are TEXT enclose them in quotes: "Green" If the variables are numeric do not use the quotes: 10 Biff "Jeff" wrote in message ... I have a large range of data that I am working with and I would like to be able to sum a colum based on two variables I have been trying to use sumif and dsum but don't really have the answer I am looking for, any suggestions? -- Jeff |
#3
|
|||
|
|||
No,
probably need to be a little more specific, I have data in one column I would like to sum based on test values in two other columns. Column A= Owner "Text" Column B = Hours "number" Column C = "Status" I would like to get a sum of hours based on the Owner and status. Example Bob has 5 hours with status pending. -- Jeff "Biff" wrote: Hi! Do you mean that you have 2 variables in the same column? If so, try one of these: =SUMIF(A1:A10,variable_1,B1:B10)+SUMIF(A1:A10,vari able_2,B1:B10) =SUMPRODUCT(--(A1:A10=variable_1)+(A1:A10=variable_2),B1:B10) If the variables are in different columns: =SUMPRODUCT(--(A1:A10=variable_1),--(B1:B10=variable_2),C1:C10) In all of the above, if the variables are TEXT enclose them in quotes: "Green" If the variables are numeric do not use the quotes: 10 Biff "Jeff" wrote in message ... I have a large range of data that I am working with and I would like to be able to sum a colum based on two variables I have been trying to use sumif and dsum but don't really have the answer I am looking for, any suggestions? -- Jeff |
#4
|
|||
|
|||
=SUMPRODUCT((a1:a99="Bob")*(c1:c99="pending")*(b1: b99))
but use your own row ranges, of course. - DH |
#5
|
|||
|
|||
Still Not working, for some reason I am getting #N/A
Does it matter if one of the selecting Columns is derived? by that I mean the following, =SUMPRODUCT((Team="Dev")*(Sprint!G3:G365="Pending" )*(Sprint!J3:J365)) Where "Pending" G3:G365, is determined by a formula. it could be complete or in progress. -- Jeff "D Hilberg" wrote: =SUMPRODUCT((a1:a99="Bob")*(c1:c99="pending")*(b1: b99)) but use your own row ranges, of course. - DH |
#6
|
|||
|
|||
Hi!
If the variables are in different columns: =SUMPRODUCT(--(A1:A10=variable_1),--(B1:B10=variable_2),C1:C10) The above formula is the same as: =SUMPRODUCT((Team="Dev")*(Sprint!G3:G365="Pending" )*(Sprint!J3:J365)) but is more efficient! Does it matter if one of the selecting Columns is derived? by that I mean the following, Where "Pending" G3:G365, is determined by a formula. it could be complete or in progress. No, it "shouldn't" matter. What does the defined name "TEAM" refer to? All ranges must be EXACTLY the same size. Biff "Jeff" wrote in message ... Still Not working, for some reason I am getting #N/A Does it matter if one of the selecting Columns is derived? by that I mean the following, =SUMPRODUCT((Team="Dev")*(Sprint!G3:G365="Pending" )*(Sprint!J3:J365)) Where "Pending" G3:G365, is determined by a formula. it could be complete or in progress. -- Jeff "D Hilberg" wrote: =SUMPRODUCT((a1:a99="Bob")*(c1:c99="pending")*(b1: b99)) but use your own row ranges, of course. - DH |
#7
|
|||
|
|||
Just out of curiosity what does the -- signify/do?
ok so I set up the formula as suggested =SUMPRODUCT(--(Team="Dev"),--(Status="Pending"),Sprint!J3:J365) Where Team =Sprint!B3:B365 Status =Sprint!G3:G365 =(INDEX(Burndown,ROW(G170)-ROW(G$2),MIN(TODAY()-SprintStart,29)*3+3)) which is in turn derived from =IF(SUMPRODUCT($I$368:M$368,$I170:M170)<0.5, "Pending", IF(M170<0.5, "Complete", "In Progress")) and the data I am trying to sum is Sprint!J3:J365 and I am getting a #Value Error. Thanks for the info Team refers to the team doing the work Team A or Team B the formula that determins the status is -- Jeff "Biff" wrote: Hi! If the variables are in different columns: =SUMPRODUCT(--(A1:A10=variable_1),--(B1:B10=variable_2),C1:C10) The above formula is the same as: =SUMPRODUCT((Team="Dev")*(Sprint!G3:G365="Pending" )*(Sprint!J3:J365)) but is more efficient! Does it matter if one of the selecting Columns is derived? by that I mean the following, Where "Pending" G3:G365, is determined by a formula. it could be complete or in progress. No, it "shouldn't" matter. What does the defined name "TEAM" refer to? All ranges must be EXACTLY the same size. Biff "Jeff" wrote in message ... Still Not working, for some reason I am getting #N/A Does it matter if one of the selecting Columns is derived? by that I mean the following, =SUMPRODUCT((Team="Dev")*(Sprint!G3:G365="Pending" )*(Sprint!J3:J365)) Where "Pending" G3:G365, is determined by a formula. it could be complete or in progress. -- Jeff "D Hilberg" wrote: =SUMPRODUCT((a1:a99="Bob")*(c1:c99="pending")*(b1: b99)) but use your own row ranges, of course. - DH |
#8
|
|||
|
|||
Hi!
Just out of curiosity what does the -- signify/do? See this: http://mcgimpsey.com/excel/formulae/doubleneg.html As far as your formulas go, they seem correct. You do have an extra set of ( ) in the Index formula but they're just being ignored. Are there any error values ( #N/A, #VALUE!, #NUM!, etc.) in any of the ranges? They will cause the problem your experiencing. Biff "Jeff" wrote in message ... Just out of curiosity what does the -- signify/do? ok so I set up the formula as suggested =SUMPRODUCT(--(Team="Dev"),--(Status="Pending"),Sprint!J3:J365) Where Team =Sprint!B3:B365 Status =Sprint!G3:G365 =(INDEX(Burndown,ROW(G170)-ROW(G$2),MIN(TODAY()-SprintStart,29)*3+3)) which is in turn derived from =IF(SUMPRODUCT($I$368:M$368,$I170:M170)<0.5, "Pending", IF(M170<0.5, "Complete", "In Progress")) and the data I am trying to sum is Sprint!J3:J365 and I am getting a #Value Error. Thanks for the info Team refers to the team doing the work Team A or Team B the formula that determins the status is -- Jeff "Biff" wrote: Hi! If the variables are in different columns: =SUMPRODUCT(--(A1:A10=variable_1),--(B1:B10=variable_2),C1:C10) The above formula is the same as: =SUMPRODUCT((Team="Dev")*(Sprint!G3:G365="Pending" )*(Sprint!J3:J365)) but is more efficient! Does it matter if one of the selecting Columns is derived? by that I mean the following, Where "Pending" G3:G365, is determined by a formula. it could be complete or in progress. No, it "shouldn't" matter. What does the defined name "TEAM" refer to? All ranges must be EXACTLY the same size. Biff "Jeff" wrote in message ... Still Not working, for some reason I am getting #N/A Does it matter if one of the selecting Columns is derived? by that I mean the following, =SUMPRODUCT((Team="Dev")*(Sprint!G3:G365="Pending" )*(Sprint!J3:J365)) Where "Pending" G3:G365, is determined by a formula. it could be complete or in progress. -- Jeff "D Hilberg" wrote: =SUMPRODUCT((a1:a99="Bob")*(c1:c99="pending")*(b1: b99)) but use your own row ranges, of course. - DH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select updated data from a range of columns | Excel Worksheet Functions | |||
date criteria to select range | Excel Worksheet Functions | |||
how to reference external refereces from a list | Excel Worksheet Functions | |||
Select same range from large number of workbooks | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions |