| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum col with reference to criteria in other col
I have foll data
criteria score Unique answer yes 4 yes 20 yes 6 no 116 yes 6 yes 4 no 45 no 57 no 6 no 8 I need is sum of all the score for each criteria Yes would give 20 No would give 116 I tried the foll... Range("b3").Value = Application.WorksheetFunction.SumIf(Range("a:a"), Range("c2"), Range("b:b")) Problem is the criteria "c2", i wish to change using a loop. i cannot use sumproduct because the criteria are more in number close to 50. Thsi something very imp for me and i am badly stuck.. please help.. Thanks! Boss |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum col with reference to criteria in other col
with data in a1:b8
excel formulas =SUMPRODUCT((A1:A8="yes")*(B1:B8)) =SUMIF(A1:A8,"=yes",B1:B8) in vb, if you just want the values range("e3").Value = application.Evaluate("SUMPRODUCT((A1:A8=" & """yes""" & ")*(B1:B8))") range("f3").Value = application.Evaluate("SUMIF(A1:A8," & """=yes""" & ",B1:B8)") -- Gary "Boss" wrote in message ... I have foll data criteria score Unique answer yes 4 yes 20 yes 6 no 116 yes 6 yes 4 no 45 no 57 no 6 no 8 I need is sum of all the score for each criteria Yes would give 20 No would give 116 I tried the foll... Range("b3").Value = Application.WorksheetFunction.SumIf(Range("a:a"), Range("c2"), Range("b:b")) Problem is the criteria "c2", i wish to change using a loop. i cannot use sumproduct because the criteria are more in number close to 50. Thsi something very imp for me and i am badly stuck.. please help.. Thanks! Boss |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum col with reference to criteria in other col
Thanks for the reply but as i said i caanot use sumproduct because i have
many criteria to sum for. i have database of 250 rows where scores of 70 emp is filled. I have to pull the total score of each employee in a different sheet. i need to find the total score of each emp.. Hope thsi makes issue clear.. please help.. Thanks a lot.. Boss "Gary Keramidas" wrote: with data in a1:b8 excel formulas =SUMPRODUCT((A1:A8="yes")*(B1:B8)) =SUMIF(A1:A8,"=yes",B1:B8) in vb, if you just want the values range("e3").Value = application.Evaluate("SUMPRODUCT((A1:A8=" & """yes""" & ")*(B1:B8))") range("f3").Value = application.Evaluate("SUMIF(A1:A8," & """=yes""" & ",B1:B8)") -- Gary "Boss" wrote in message ... I have foll data criteria score Unique answer yes 4 yes 20 yes 6 no 116 yes 6 yes 4 no 45 no 57 no 6 no 8 I need is sum of all the score for each criteria Yes would give 20 No would give 116 I tried the foll... Range("b3").Value = Application.WorksheetFunction.SumIf(Range("a:a"), Range("c2"), Range("b:b")) Problem is the criteria "c2", i wish to change using a loop. i cannot use sumproduct because the criteria are more in number close to 50. Thsi something very imp for me and i am badly stuck.. please help.. Thanks! Boss |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum col with reference to criteria in other col
give an example of the data so we can help.
"Boss" wrote in message ... Thanks for the reply but as i said i caanot use sumproduct because i have many criteria to sum for. i have database of 250 rows where scores of 70 emp is filled. I have to pull the total score of each employee in a different sheet. i need to find the total score of each emp.. Hope thsi makes issue clear.. please help.. Thanks a lot.. Boss "Gary Keramidas" wrote: with data in a1:b8 excel formulas =SUMPRODUCT((A1:A8="yes")*(B1:B8)) =SUMIF(A1:A8,"=yes",B1:B8) in vb, if you just want the values range("e3").Value = application.Evaluate("SUMPRODUCT((A1:A8=" & """yes""" & ")*(B1:B8))") range("f3").Value = application.Evaluate("SUMIF(A1:A8," & """=yes""" & ",B1:B8)") -- Gary "Boss" wrote in message ... I have foll data criteria score Unique answer yes 4 yes 20 yes 6 no 116 yes 6 yes 4 no 45 no 57 no 6 no 8 I need is sum of all the score for each criteria Yes would give 20 No would give 116 I tried the foll... Range("b3").Value = Application.WorksheetFunction.SumIf(Range("a:a"), Range("c2"), Range("b:b")) Problem is the criteria "c2", i wish to change using a loop. i cannot use sumproduct because the criteria are more in number close to 50. Thsi something very imp for me and i am badly stuck.. please help.. Thanks! Boss |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum col with reference to criteria in other col
give an example of the data so we can help.
-- Gary "Boss" wrote in message ... I have foll data criteria score Unique answer yes 4 yes 20 yes 6 no 116 yes 6 yes 4 no 45 no 57 no 6 no 8 I need is sum of all the score for each criteria Yes would give 20 No would give 116 I tried the foll... Range("b3").Value = Application.WorksheetFunction.SumIf(Range("a:a"), Range("c2"), Range("b:b")) Problem is the criteria "c2", i wish to change using a loop. i cannot use sumproduct because the criteria are more in number close to 50. Thsi something very imp for me and i am badly stuck.. please help.. Thanks! Boss |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum col with reference to criteria in other col
EMP name Score
Leo Brown 9 Leo Brown 9 Leo Brown 9 Leo Brown 9 Leo Brown 9 Leo Brown 9 Leo Brown 10 Andrea Jones 10 Paul Adams 10 Andrea Jones 10 Andrea Jones 10 Andrea Jones 10 Andrea Jones 13 Leo Brown 13 Leo Brown 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 18 Bob Hudson 18 Andrea Jones 20 Andrea Jones 20 Andrea Jones 21 Leo Brown 21 Leo Brown 21 Leo Brown 21 Leo Brown 23 Leo Brown 43 Leo Brown 22 Leo Brown 12 It continues till 70 emp with more than 250 records. I need the total score of each emp in other sheet. It is a huge project on which i am working, entire coding is done i am stuck up in this.. please help.. Thanks! Boss "Gary Keramidas" wrote: give an example of the data so we can help. "Boss" wrote in message ... Thanks for the reply but as i said i caanot use sumproduct because i have many criteria to sum for. i have database of 250 rows where scores of 70 emp is filled. I have to pull the total score of each employee in a different sheet. i need to find the total score of each emp.. Hope thsi makes issue clear.. please help.. Thanks a lot.. Boss "Gary Keramidas" wrote: with data in a1:b8 excel formulas =SUMPRODUCT((A1:A8="yes")*(B1:B8)) =SUMIF(A1:A8,"=yes",B1:B8) in vb, if you just want the values range("e3").Value = application.Evaluate("SUMPRODUCT((A1:A8=" & """yes""" & ")*(B1:B8))") range("f3").Value = application.Evaluate("SUMIF(A1:A8," & """=yes""" & ",B1:B8)") -- Gary "Boss" wrote in message ... I have foll data criteria score Unique answer yes 4 yes 20 yes 6 no 116 yes 6 yes 4 no 45 no 57 no 6 no 8 I need is sum of all the score for each criteria Yes would give 20 No would give 116 I tried the foll... Range("b3").Value = Application.WorksheetFunction.SumIf(Range("a:a"), Range("c2"), Range("b:b")) Problem is the criteria "c2", i wish to change using a loop. i cannot use sumproduct because the criteria are more in number close to 50. Thsi something very imp for me and i am badly stuck.. please help.. Thanks! Boss |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum col with reference to criteria in other col
do you need them summarized on 1 sheet, or does everybody have their own sheet?
-- Gary "Boss" wrote in message ... EMP name Score Leo Brown 9 Leo Brown 9 Leo Brown 9 Leo Brown 9 Leo Brown 9 Leo Brown 9 Leo Brown 10 Andrea Jones 10 Paul Adams 10 Andrea Jones 10 Andrea Jones 10 Andrea Jones 10 Andrea Jones 13 Leo Brown 13 Leo Brown 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 18 Bob Hudson 18 Andrea Jones 20 Andrea Jones 20 Andrea Jones 21 Leo Brown 21 Leo Brown 21 Leo Brown 21 Leo Brown 23 Leo Brown 43 Leo Brown 22 Leo Brown 12 It continues till 70 emp with more than 250 records. I need the total score of each emp in other sheet. It is a huge project on which i am working, entire coding is done i am stuck up in this.. please help.. Thanks! Boss "Gary Keramidas" wrote: give an example of the data so we can help. "Boss" wrote in message ... Thanks for the reply but as i said i caanot use sumproduct because i have many criteria to sum for. i have database of 250 rows where scores of 70 emp is filled. I have to pull the total score of each employee in a different sheet. i need to find the total score of each emp.. Hope thsi makes issue clear.. please help.. Thanks a lot.. Boss "Gary Keramidas" wrote: with data in a1:b8 excel formulas =SUMPRODUCT((A1:A8="yes")*(B1:B8)) =SUMIF(A1:A8,"=yes",B1:B8) in vb, if you just want the values range("e3").Value = application.Evaluate("SUMPRODUCT((A1:A8=" & """yes""" & ")*(B1:B8))") range("f3").Value = application.Evaluate("SUMIF(A1:A8," & """=yes""" & ",B1:B8)") -- Gary "Boss" wrote in message ... I have foll data criteria score Unique answer yes 4 yes 20 yes 6 no 116 yes 6 yes 4 no 45 no 57 no 6 no 8 I need is sum of all the score for each criteria Yes would give 20 No would give 116 I tried the foll... Range("b3").Value = Application.WorksheetFunction.SumIf(Range("a:a"), Range("c2"), Range("b:b")) Problem is the criteria "c2", i wish to change using a loop. i cannot use sumproduct because the criteria are more in number close to 50. Thsi something very imp for me and i am badly stuck.. please help.. Thanks! Boss |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum col with reference to criteria in other col
ok, try this. i assumed that the sheetnames were sheet1 and sheet2. that the
names start in a2 and the scores in b2 on sheet1. there are column headings on sheet2, name and score. i assumed i could use c1 on sheet1 for a subtotal formula, move it if you have something in c1 and change the references to c1. watch for word-wrap in the post, there are no line breaks in the code Sub consolidate() Dim enames As Collection Dim lastrow As Long Dim j As Long, i As Long Dim c As Range Dim empStr As String Dim ws As Worksheet Dim ws2 As Worksheet Dim escore As Double i = 2 Set ws = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row ws.Range("C1").Formula = "=subtotal(109,b2:b" & lastrow & ")" Set enames = New Collection For Each c In ws.Range("A2:A" & lastrow) On Error Resume Next empStr = Trim(c.Value) enames.Add empStr, CStr(empStr) On Error GoTo 0 Next For j = 1 To enames.Count With ws.Range("A1:B" & lastrow) .AutoFilter Field:=1, Criteria1:=enames(j), Operator:=xlAnd End With Debug.Print enames(j) escore = ws.Range("C1").Value ws2.Range("A" & i) = enames(j) ws2.Range("B" & i) = ws.Range("C1").Value i = i + 1 Next ws.AutoFilterMode = False End Sub -- Gary "Boss" wrote in message ... EMP name Score Leo Brown 9 Leo Brown 9 Leo Brown 9 Leo Brown 9 Leo Brown 9 Leo Brown 9 Leo Brown 10 Andrea Jones 10 Paul Adams 10 Andrea Jones 10 Andrea Jones 10 Andrea Jones 10 Andrea Jones 13 Leo Brown 13 Leo Brown 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 18 Bob Hudson 18 Andrea Jones 20 Andrea Jones 20 Andrea Jones 21 Leo Brown 21 Leo Brown 21 Leo Brown 21 Leo Brown 23 Leo Brown 43 Leo Brown 22 Leo Brown 12 It continues till 70 emp with more than 250 records. I need the total score of each emp in other sheet. It is a huge project on which i am working, entire coding is done i am stuck up in this.. please help.. Thanks! Boss "Gary Keramidas" wrote: give an example of the data so we can help. "Boss" wrote in message ... Thanks for the reply but as i said i caanot use sumproduct because i have many criteria to sum for. i have database of 250 rows where scores of 70 emp is filled. I have to pull the total score of each employee in a different sheet. i need to find the total score of each emp.. Hope thsi makes issue clear.. please help.. Thanks a lot.. Boss "Gary Keramidas" wrote: with data in a1:b8 excel formulas =SUMPRODUCT((A1:A8="yes")*(B1:B8)) =SUMIF(A1:A8,"=yes",B1:B8) in vb, if you just want the values range("e3").Value = application.Evaluate("SUMPRODUCT((A1:A8=" & """yes""" & ")*(B1:B8))") range("f3").Value = application.Evaluate("SUMIF(A1:A8," & """=yes""" & ",B1:B8)") -- Gary "Boss" wrote in message ... I have foll data criteria score Unique answer yes 4 yes 20 yes 6 no 116 yes 6 yes 4 no 45 no 57 no 6 no 8 I need is sum of all the score for each criteria Yes would give 20 No would give 116 I tried the foll... Range("b3").Value = Application.WorksheetFunction.SumIf(Range("a:a"), Range("c2"), Range("b:b")) Problem is the criteria "c2", i wish to change using a loop. i cannot use sumproduct because the criteria are more in number close to 50. Thsi something very imp for me and i am badly stuck.. please help.. Thanks! Boss |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum col with reference to criteria in other col
TOO GOOD....
You made my day... Thanks! Boss "Gary Keramidas" wrote: ok, try this. i assumed that the sheetnames were sheet1 and sheet2. that the names start in a2 and the scores in b2 on sheet1. there are column headings on sheet2, name and score. i assumed i could use c1 on sheet1 for a subtotal formula, move it if you have something in c1 and change the references to c1. watch for word-wrap in the post, there are no line breaks in the code Sub consolidate() Dim enames As Collection Dim lastrow As Long Dim j As Long, i As Long Dim c As Range Dim empStr As String Dim ws As Worksheet Dim ws2 As Worksheet Dim escore As Double i = 2 Set ws = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row ws.Range("C1").Formula = "=subtotal(109,b2:b" & lastrow & ")" Set enames = New Collection For Each c In ws.Range("A2:A" & lastrow) On Error Resume Next empStr = Trim(c.Value) enames.Add empStr, CStr(empStr) On Error GoTo 0 Next For j = 1 To enames.Count With ws.Range("A1:B" & lastrow) .AutoFilter Field:=1, Criteria1:=enames(j), Operator:=xlAnd End With Debug.Print enames(j) escore = ws.Range("C1").Value ws2.Range("A" & i) = enames(j) ws2.Range("B" & i) = ws.Range("C1").Value i = i + 1 Next ws.AutoFilterMode = False End Sub -- Gary "Boss" wrote in message ... EMP name Score Leo Brown 9 Leo Brown 9 Leo Brown 9 Leo Brown 9 Leo Brown 9 Leo Brown 9 Leo Brown 10 Andrea Jones 10 Paul Adams 10 Andrea Jones 10 Andrea Jones 10 Andrea Jones 10 Andrea Jones 13 Leo Brown 13 Leo Brown 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 13 Andrea Jones 18 Bob Hudson 18 Andrea Jones 20 Andrea Jones 20 Andrea Jones 21 Leo Brown 21 Leo Brown 21 Leo Brown 21 Leo Brown 23 Leo Brown 43 Leo Brown 22 Leo Brown 12 It continues till 70 emp with more than 250 records. I need the total score of each emp in other sheet. It is a huge project on which i am working, entire coding is done i am stuck up in this.. please help.. Thanks! Boss "Gary Keramidas" wrote: give an example of the data so we can help. "Boss" wrote in message ... Thanks for the reply but as i said i caanot use sumproduct because i have many criteria to sum for. i have database of 250 rows where scores of 70 emp is filled. I have to pull the total score of each employee in a different sheet. i need to find the total score of each emp.. Hope thsi makes issue clear.. please help.. Thanks a lot.. Boss "Gary Keramidas" wrote: with data in a1:b8 excel formulas =SUMPRODUCT((A1:A8="yes")*(B1:B8)) =SUMIF(A1:A8,"=yes",B1:B8) in vb, if you just want the values range("e3").Value = application.Evaluate("SUMPRODUCT((A1:A8=" & """yes""" & ")*(B1:B8))") range("f3").Value = application.Evaluate("SUMIF(A1:A8," & """=yes""" & ",B1:B8)") -- Gary "Boss" wrote in message ... I have foll data criteria score Unique answer yes 4 yes 20 yes 6 no 116 yes 6 yes 4 no 45 no 57 no 6 no 8 I need is sum of all the score for each criteria Yes would give 20 No would give 116 I tried the foll... Range("b3").Value = Application.WorksheetFunction.SumIf(Range("a:a"), Range("c2"), Range("b:b")) Problem is the criteria "c2", i wish to change using a loop. i cannot use sumproduct because the criteria are more in number close to 50. Thsi something very imp for me and i am badly stuck.. please help.. Thanks! Boss |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Filter criteria in reference cells | Excel Discussion (Misc queries) | |||
| To criteria with Reference from Cell | Excel Worksheet Functions | |||
| Reference cell in one TAB from another using two criteria | Excel Worksheet Functions | |||
| sumif criteria reference | Excel Worksheet Functions | |||
| 3D reference with multiple criteria | Excel Worksheet Functions | |||
Linear Mode
