Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct and Beyond
Hello
I am relatively new to VB and need help. I am not quite sure how to accomplish this.... I need routine that will find the first blank cell in Col H and insert a sumproduct type formula. That will sum all of the cells in Col H where the value in the same row in col G is equal to "o" and the value in col F contains "F". I also need to replicate this over 20 columns in the same row. I am still trying to learn how to use sumproduct so if the formula use would be different, what would this be? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct and Beyond
This does it for H,
Dim iLastRow As Long iLastRow = Range("H1").End(xlDown).Row If Range("H1").Value < "" Then Cells(iLastRow + 1, "H").Formula = _ "=SUMPRODUCT(--(F1:F" & iLastRow & "=""F"")," & _ "--(G1:G" & iLastRow & "=""o""))" End If but a couple of questions. What if H6 is the first blank in H, but F10 and G10 have values? If you want to extend to say I, J, K the data will be overlappiong. What are the other columns? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Sally" wrote in message ... Hello I am relatively new to VB and need help. I am not quite sure how to accomplish this.... I need routine that will find the first blank cell in Col H and insert a sumproduct type formula. That will sum all of the cells in Col H where the value in the same row in col G is equal to "o" and the value in col F contains "F". I also need to replicate this over 20 columns in the same row. I am still trying to learn how to use sumproduct so if the formula use would be different, what would this be? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct and Beyond
This macro does the first part of the job, but it's not clear for me what do
you want to replicate? This Sumproduct formula with the same column references (H,G,F) or with other column references (if so, which columns)? Sub sumprodtest() Columns("H:H").Find(What:="*", _ After:=Range("H1"), _ LookAt:=xlPart, _ LookIn:=xlValues, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Activate lastfilled = ActiveCell.Row Range("H" & lastfilled + 1).FormulaR1C1 = _ "=SUMPRODUCT(R[-" & lastfilled & "]C:R[-1]C,--(R[-" & lastfilled & "]C[-1]:R[-1]C[-1]=""o""),--(NOT(ISERROR(SEARCH(""F"",R[-" & lastfilled & "]C[-2]:R[-1]C[-2])))))" End Sub Regards, Stefi €žSally€ť ezt Ă*rta: Hello I am relatively new to VB and need help. I am not quite sure how to accomplish this.... I need routine that will find the first blank cell in Col H and insert a sumproduct type formula. That will sum all of the cells in Col H where the value in the same row in col G is equal to "o" and the value in col F contains "F". I also need to replicate this over 20 columns in the same row. I am still trying to learn how to use sumproduct so if the formula use would be different, what would this be? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct and Beyond
Bob
Thanks for the reply and sorry for not enogh info. The file is imported so I dont think that the what ifs will happen. This is the data layout Rows 1 & 2 are header information. A3:F3 is data as every 4th row. H3:AA6 are are numeric entries. These are the values I am trying to total. The numbers of rows will vary from day to day but in general H1:AAx (However many rows there are) will be full. I need the total for each row H:AA based on the same criteria Col G= "o" and Col F contains "F". I hope this helps and doesnt add furhter confusion Thanks! "Bob Phillips" wrote: This does it for H, Dim iLastRow As Long iLastRow = Range("H1").End(xlDown).Row If Range("H1").Value < "" Then Cells(iLastRow + 1, "H").Formula = _ "=SUMPRODUCT(--(F1:F" & iLastRow & "=""F"")," & _ "--(G1:G" & iLastRow & "=""o""))" End If but a couple of questions. What if H6 is the first blank in H, but F10 and G10 have values? If you want to extend to say I, J, K the data will be overlappiong. What are the other columns? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Sally" wrote in message ... Hello I am relatively new to VB and need help. I am not quite sure how to accomplish this.... I need routine that will find the first blank cell in Col H and insert a sumproduct type formula. That will sum all of the cells in Col H where the value in the same row in col G is equal to "o" and the value in col F contains "F". I also need to replicate this over 20 columns in the same row. I am still trying to learn how to use sumproduct so if the formula use would be different, what would this be? Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct and Beyond
The code works for H but is counting not totaling. and how do I copy it
across the other 19 cells Thanks! "Bob Phillips" wrote: This does it for H, Dim iLastRow As Long iLastRow = Range("H1").End(xlDown).Row If Range("H1").Value < "" Then Cells(iLastRow + 1, "H").Formula = _ "=SUMPRODUCT(--(F1:F" & iLastRow & "=""F"")," & _ "--(G1:G" & iLastRow & "=""o""))" End If but a couple of questions. What if H6 is the first blank in H, but F10 and G10 have values? If you want to extend to say I, J, K the data will be overlappiong. What are the other columns? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Sally" wrote in message ... Hello I am relatively new to VB and need help. I am not quite sure how to accomplish this.... I need routine that will find the first blank cell in Col H and insert a sumproduct type formula. That will sum all of the cells in Col H where the value in the same row in col G is equal to "o" and the value in col F contains "F". I also need to replicate this over 20 columns in the same row. I am still trying to learn how to use sumproduct so if the formula use would be different, what would this be? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct | Excel Discussion (Misc queries) | |||
=SUMPRODUCT | Excel Discussion (Misc queries) |