Hi all I am trying to add a countifs formula to a running macro, but so far I have not had much success and wonder if anyone could help pleas. I have posted my query on another website without reply, and I will make sure I post this link on there so people are aware I have asked on more than one website. I am sure it is something simple I am missing but it is starting to get frustrating so need some help.
This is the formula I have set up, but as it is on many cells it is slowing the workbook down. [code]* =COUNTIFS('g2 graded'!$G$1:$G$20000, C2, 'g2 graded'!$D$1:$D$20000, 1) *[/code] * I tried this, which is similar to my countif formulas in the macro but it doesnt work.* [code] * Cells(x, 20).Value = Application.WorksheetFunction.CountIfs(Sheets("g2 graded").Range("g1:g30000"), Cells(x, 3), Sheets("g2 graded").Range("d1:d30000"), "1""") *[/code] My full running macro is: [code] * Sub UpdateValues() Application.ScreenUpdating = False Dim cell As Range For x = 2 To 10000 'loop through rows from row 2 to the last row 'If cell isn't blue then macro assumes it doesn't need to continue (because it has reached the end of the blue cells) If Cells(x, 18).Interior.ColorIndex <> 37 Then End Else 'insert value for column U (G2 Graded May). 'note that we don't calculate value for columns R & S until last because they depend on the calculated values of other columns Cells(x, 21).Value = Application.WorksheetFunction.CountIf(Sheets("g2 graded").Range("G1:G10000"), Cells(x, 3).Value) 'x refers to the current row in the loop and 21 is the column number 'insert value for column V (G2 Graded May). Cells(x, 22).Value = Application.WorksheetFunction.SumIf(Sheets("g2 graded").Range("G1:G10000"), Cells(x, 3).Value, Sheets("g2 graded").Range("N1:N10000")) 'insert value for column W (G2 GradedTotal). Cells(x, 23).Value = Application.WorksheetFunction.CountIf(Sheets("g2 graded april ").Range("H1:H10000"), Cells(x, 3).Value) + Cells(x, 21).Value 'x refers to the current row in the loop and 21 is the column number 'insert value for column X (G2 GradedTotal). Cells(x, 24).Value = Application.WorksheetFunction.SumIf(Sheets("g2 graded april ").Range("H1:H10000"), Cells(x, 3).Value, Sheets("g2 graded april ").Range("O1:O10000")) + Cells(x, 22).Value 'insert value for column Z (G2 Claim May). Cells(x, 26).Value = Application.WorksheetFunction.CountIf(Sheets("g2 claiming").Range("F1:F10000"), Cells(x, 3).Value) 'insert value for column AA (G2 Claim May). Cells(x, 27).Value = Application.WorksheetFunction.SumIf(Sheets("g2 claiming").Range("F1:F10000"), Cells(x, 3).Value, Sheets("g2 claiming").Range("AA1:AA10000")) 'insert value for column AB (G2 Claim Total). Cells(x, 28).Value = Application.WorksheetFunction.CountIf(Sheets("g2 claiming april").Range("G1:G10000"), Cells(x, 3).Value) 'insert value for column AC (G2 Claim Total). Cells(x, 29).Value = Application.WorksheetFunction.SumIf(Sheets("g2 claiming april").Range("G1:G10000"), Cells(x, 3).Value, Sheets("g2 claiming april").Range("AB1:AB10000")) 'insert value for column R (Graded Total). Cells(x, 18).Value = Cells(x, 23).Value + Cells(x, 28).Value 'insert value for column S (Claiming Total). Cells(x, 19).Value = Cells(x, 24).Value + Cells(x, 29).Value End If Next Application.ScreenUpdating = True End Sub * [/code] The webpage it is on is http://www.excelforum.com/excel-worksheet-functions/776036-converting-countifs-formula-to-macro.html Any help would be greatly appreciated. Regards Gemma -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel