Using name ranges with sumproduct?

[Lawrence]

= SUMPRODUCT(--(DAY(Sheet1!$F$2:$F$65536)=DAY(H$1)), --(Sheet1!$R$2:$R$65536=$C2), Sheet1!$AF$2:$AF$65536)
+ SUMPRODUCT(--(DAY(Sheet2!$F$2:$F$65536)=DAY(H$1)), --(Sheet2!$R$2:$R$65536=$C2), Sheet2!$AF$2:$AF$65536)

The first part matches the day in H1 against column F in Sheet1
The second part matches the name in C2 against column R in Sheet 1
The last part adds the scores for the above 2 conditions.
Then repeat for Sheet2

The problems are: 1) The names I am checking against can also be in columns T and V, and 2) the scores to be returned can also be in columns AG and AH.

Ultimately, I?d like to end up with something like this:
If the date in Sheet1 and Sheet 2 column F matches the date in H1
And the name in Sheet1 and Sheet2 columns R,T,V matches the name in C2
Add the scores in Sheet1 and Sheet2 columns AF,AG,AH

Can I use name ranges for dates and names to prevent some ugly formula? I have been unsuccessful in my attempts so far.

[Bob Phillips]

=SUMPRODUCT((DAY(Sheet1!$F$2:$F$65)=DAY(H$1))*((Sheet1!$R$2:$R$65=$C2)+(She et1!$T$2:$T$65=$C2)+(Sheet1!$V$2:$V$65=$C2))*(Sheet1!$AF$2:$AH$65))
+SUMPRODUCT((DAY(Sheet2!$F$2:$F$65)=DAY(H$1))*((Sheet2!$R$2:$R$65=$C2)+(She et2!$T$2:$T$65=$C2)+(Sheet2!$V$2:$V$65=$C2))*(Sheet2!$AF$2:$AH$65))

[Lawrence]

Thanks Bob, but I get a #VALUE! Attached is a sample with your formula in it.

My data set is really ugly and it needs to be rearranged in a simpler form to reduce the file size, hence what I am trying to do. As it stands, the data set is split between Sheet1 and Sheet2 as it contains 80K to 100K rows and about 40 columns. With a file size greater than 45Mb, it makes it very unstable and I also can’t do much with the file as when I try to calc anything it bombs out.

So, the end result is to generate and save a Summary worksheet in another workbook so that I can do all my graphs, tables, and various calcs. Every week, I would then receive a new data set and update the links in the Summary table.

Is there a better way to do this?

[Bob Phillips]

It is the blank cells Lawrence. If they were zeroes, it would work. But this will get enormously slow when you extend the range and copy for all datesand all employees.

[Lawrence]

Thanks. I have to figure out how to extract the relevant information without excel locking up on me all the time.

[Lawrence]

Alright, so I have done the following to reduce the size of the data file. I am sure there is a cleaner way to write this code, but it works.



Option Explicit
Dim DataTotalRows As Long

Private Sub CommandButton1_Click()
DataTotalRows = LastRow
Range("A2:G65536").ClearContents
ReduceData
End Sub

Private Function LastRow()
Dim DataTotalRows As Long
DataTotalRows = 1
Do Until Worksheets("2008.04b").Cells(DataTotalRows, 1).Value = ""
DataTotalRows = DataTotalRows + 1
Loop
LastRow = DataTotalRows - 1
End Function

Private Sub Worksheet_Activate()
End Sub

Private Sub ReduceData()
Dim Date1 As String
Dim Name1 As String
Dim Name2 As String
Dim Name3 As String
Dim Pts1 As String
Dim Pts2 As String
Dim Pts3 As String
Dim OutputA As String
Dim OutputB As String
Dim OutputC As String
Dim OutputD As String
Dim OutputE As String
Dim OutputF As String
Dim OutputG As String
Dim LValue As String
Dim RowIndex As Long
Dim i As Long
Dim k As Long
Dim l As Long
i = 0
k = 2
OutputA = "A2"
OutputB = "B2"
OutputC = "C2"
OutputD = "D2"
OutputE = "E2"
OutputF = "F2"
OutputG = "G2"
RowIndex = 2
Do Until i = DataTotalRows

Date1 = Worksheets("2008.04b").Cells(RowIndex, 6).Value
Name1 = Worksheets("2008.04b").Cells(RowIndex, 18).Value
Name2 = Worksheets("2008.04b").Cells(RowIndex, 20).Value
Name3 = Worksheets("2008.04b").Cells(RowIndex, 22).Value
Pts1 = Worksheets("2008.04b").Cells(RowIndex, 32).Value
Pts2 = Worksheets("2008.04b").Cells(RowIndex, 33).Value
Pts3 = Worksheets("2008.04b").Cells(RowIndex, 34).Value

Worksheets(Me.Name).Range(OutputA).Value = Date1
Worksheets(Me.Name).Range(OutputB).Value = Name1
Worksheets(Me.Name).Range(OutputC).Value = Name2
Worksheets(Me.Name).Range(OutputD).Value = Name3
Worksheets(Me.Name).Range(OutputE).Value = Pts1
Worksheets(Me.Name).Range(OutputF).Value = Pts2
Worksheets(Me.Name).Range(OutputG).Value = Pts3

i = i + 1
k = k + 1
RowIndex = RowIndex + 1
LValue = Str(k)
OutputA = "A" & Trim(LValue)
OutputB = "B" & Trim(LValue)
OutputC = "C" & Trim(LValue)
OutputD = "D" & Trim(LValue)
OutputE = "E" & Trim(LValue)
OutputF = "F" & Trim(LValue)
OutputG = "G" & Trim(LValue)

Loop

End Sub


However, I'd like to also delete rows where columns B trough G are blank. I tried to do it, but it seems the cells are not really blank even though there is nothing in them.



Sub DelBlanks()
ActiveSheet.Range("B:G").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

[Bob Phillips]

I would add a formula to a spare column, a formula of

=COUNTIF(B2:G2,"")=6

and autofilter that column for TRUE, then delete all visible rows.