Sum If/ Data output problems

[lanhao]

Well, thanks for the help with the sumif function work that I asked for on the last post, however, all is not right with my world ^^;

What I need to do, is basically have this part of hte module set to grab information from one sheet, and port it over to another sheet. There are a few things that it needs to take into account when it's putting it onto the new sheet:

First, it is date sensitive, so it's looking for information in there for all entries within two dates. Secondly, it (and this is the reason why I used the SumiIf function) was that it added up all the information within those ranged (i don't think sumproduct would help here based on what it does). Lastly, I would rather the formulas be placed in VB format instead of a cell = "<insert function here>" method. (I got the syntax for the sumif if it was on the same sheet, but i honestly don't know how to have it look for a different sheet).

I included the 'For' method that i managed it to look up all information for each of the reps on here, but it unfortunately is not searching only for the date range.

Here is the code:

Sub OverallQC()
Set sht6 = Worksheets("Overall Stats")
Set sht7 = Worksheets("QCInfo")
Set sht8 = Worksheets("DataDump")
Dim i As Integer 'row counter for the data page "QCInfo" for finding last row
Dim j As Integer 'row counter for the data page "Overall Stats"
Dim k As Long 'row counter for the data page "DataDump"
i = 1
j = 10
k = 1
Application.ScreenUpdating = False
'Clear all information
Range("A10:D150").Select
Range("A10").Activate
Selection.ClearContents
Range("B6:D6").Select
Selection.ClearContents
Application.Goto Reference:=Range("A10"), Scroll:=False

'Add the updated list of reps
Sheets("DataDump").Select
Range(Cells(2, 3), Cells(Rows.Count, 3).End(xlUp)).Select
Selection.Copy
Sheets("Overall Stats").Select
Range("A10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:=Range("A10"), Scroll:=False

Do While IsEmpty(sht7.Cells(i, 1).Value) = False 'keep doing until we have searched through the whole data page "Comments"

If sht6.Range("C3") <= sht7.Range("B" & i) And sht6.Range("D3") >= sht7.Range("B" & i) Then 'this places the data if the names match
sht6.Range("B" & j) = "=IF(RC[1]=0,0,RC[1]/RC[2])"
sht6.Range("C" & j) = "=SUMIF(QCInfo!C1,RC1,QCInfo!C3)"
sht6.Range("D" & j) = "=SUMIF(QCInfo!C1,RC1,QCInfo!C4)"
j = j + 1
End If
i = i + 1

Loop

' For k = 10 To Cells(Rows.Count, "A").End(xlUp).Row
' If sht6.Cells(k, "A").Value = "" Then
' sht6.Range("B" & k) = "N/A"
' sht6.Range("C" & k) = "0"
' sht6.Range("D" & k) = "0"
' Else
' sht6.Range("B" & j) = "=IF(RC[1]=0,0,RC[1]/RC[2])"
' sht6.Range("C" & j) = "=SUMIF(QCInfo!C1,RC1,QCInfo!C3)"
' sht6.Range("D" & j) = "=SUMIF(QCInfo!C1,RC1,QCInfo!C4)"
' End If
' Next k
sht6.Range("C6") = Application.WorksheetFunction.Sum(Range(Cells(10, 3), Cells(Rows.Count, 3).End(xlUp)))
sht6.Range("D6") = Application.WorksheetFunction.Sum(Range(Cells(10, 4), Cells(Rows.Count, 4).End(xlUp)))
sht6.Range("B6") = "=IF(RC[1]=0,0,RC[1]/RC[2])"
Range(Cells(10, 1), Cells(Rows.Count, 4).End(xlUp)).Select
Selection.Sort Key1:=Range("B10"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A10").Select
Application.ScreenUpdating = True
End Sub

Thanks for all help that can be provided on this.

[acw]

Hi

Can you please put up a small sample file which shows the structure and has some example data. Show what you want to happend for what particular circumstance.


Tony

[XLGibbs]

SOunds like SUMPRODUCT would work fine for a two criteria lookup using dates....

A sample file with sanitized data would be useful....

[lanhao]

Here is a really stripped down thing, the two cells with the dates on the overall stats page are actually tied to a calendar popup on my primary one. Just been running into a few different issues with getting the info up there, so that's for being willing to review it. :)

[lanhao]

the file would also need to be able to match by name (which is why i was thinking the same thing) but I have been stumbling through this one quite a bit ^^;

[acw]

Hi

The following formulas in C10 and D10 should give you the results you are chasing. Try them manually and confirm they are correct. If so, then adapt the macro formula generation process to the new formulas.

C10:
=SUMPRODUCT(--(QCInfo!$A$2:$A$30='Overall Stats'!$A10),--(QCInfo!$B$2:$B$30>='Overall Stats'!$C$3),--(QCInfo!$B$2:$B$30<='Overall Stats'!$D$3),QCInfo!$C$2:$C$30)
D10:
=SUMPRODUCT(--(QCInfo!$A$2:$A$30='Overall Stats'!$A10),--(QCInfo!$B$2:$B$30>='Overall Stats'!$C$3),--(QCInfo!$B$2:$B$30<='Overall Stats'!$D$3),QCInfo!$D$2:$D$30)


Tony

[lanhao]

Thanks so much!

I'm trying this out, but how would i go about putting that in there in VBA format since it's asking for the RC format instead of specific cell numbers?