Should I use sumproduct function?

  • Thread starter Thread starter matrix7410
  • Start date Start date
M

matrix7410

Hello,
I have the following table on sheet1.
State Jan08 Feb08 Mar08
Ny 1000 1500 1800
NJ 8000 2500 3000
-
-

How do I bring in the above info to the below format on sheet2 without
having to do copy and paste. I would love to use some kind of
function that will compare state and the month and will give me the
numbers as the output.

State Jan08 Feb08 Mar08
Ny 1000 1500 1800
Growth
Gross
Net
# of Leads
NJ 8000 2500 3000
Growth
Gross
Net
# of Leads

thanks!
 
Are these rows already there? If, do they have any data? You could use a
looping macro to copy and then paste 4 rows down from the last row on the
destination sheet.

Growth
Gross
Net
# of Leads
 
You really just need a simple macro.

Sub CreateSheet2()

RowCount = 2
NewRowCount = 2

'copy header row from sheet1 to sheet2
Sheets("Sheet1").Rows(1).Copy _
Destination:=Sheets("Sheet2").Rows(1)

With Sheets("Sheet1")
Do While .Range("A" & RowCount) <> ""
.Rows(RowCount).Copy
With Sheets("Sheet2")
.Rows(NewRowCount).Paste
.Range("A" & (NewRowCount + 1)) = "Growth"
.Range("A" & (NewRowCount + 2)) = "Gross"
.Range("A" & (NewRowCount + 3)) = "Net"
.Range("A" & (NewRowCount + 4)) = "# Number of Leads"
NewRowCount = NewRowCount + 5
End With
RowCount = RowCount + 1
Loop
End With

End Sub
 
You really just need a simple macro.

Sub CreateSheet2()

RowCount = 2
NewRowCount = 2

'copy header row from sheet1 to sheet2
Sheets("Sheet1").Rows(1).Copy _
   Destination:=Sheets("Sheet2").Rows(1)

With Sheets("Sheet1")
   Do While .Range("A" & RowCount) <> ""
      .Rows(RowCount).Copy
      With Sheets("Sheet2")
         .Rows(NewRowCount).Paste
         .Range("A" & (NewRowCount + 1)) = "Growth"
         .Range("A" & (NewRowCount + 2)) = "Gross"
         .Range("A" & (NewRowCount + 3)) = "Net"
         .Range("A" & (NewRowCount + 4)) = "# Number of Leads"
         NewRowCount = NewRowCount + 5
      End With
      RowCount = RowCount + 1
   Loop
End With

End Sub








- Show quoted text -
Click to expand...

I got the answer without having to use the macro. I used sumproduct
function. Here is the formula that I've used
=SUMPRODUCT(($B$8:$K$8=B1)*($A$9:$A$22=A1)*($B$9:$K$22))
 
Back
Top