Sum corresponding cells in col 2 if cels match in col1

[fbogus]

I would like to sum the value in column2 if the value is >5 when the values for Name match the previous row. So for abba I would sum 2 of the 4 values and place the result into a column 5 columns to the right. Can this be done in a formula or best as a macro?

Ideally I'd like to do something like
Worksheets(X).Range("G2:G" & Lastrow).Formula = "=whateverformula"

I'm just not sure how to approach this.


<table style="border-collapse: collapse; width: 267pt;" width="354" border="0" cellpadding="0" cellspacing="0"><col style="width: 43pt;" width="57"> <col style="width: 43pt;" width="57" span="2"> <col style="width: 46pt;" width="61" span="3"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 43pt;" width="57" height="20">Name</td> <td class="xl66" style="width: 43pt;" width="57">item</td> <td class="xl66" style="width: 43pt;" width="57">Position</td> <td class="xl66" style="width: 46pt;" width="61">length1</td> <td class="xl66" style="width: 46pt;" width="61">length2</td> <td class="xl66" style="width: 46pt;" width="61">Num</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">abba</td> <td class="xl64">1</td> <td class="xl64">1.1E+09</td> <td class="xl64">3702524</td> <td class="xl65" style="background: rgb(255, 255, 204) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">4.1400</td> <td class="xl64" style="background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">191</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">abba</td> <td class="xl64">9</td> <td class="xl64">9E+09</td> <td class="xl64">32072905</td> <td class="xl65">5.3314</td> <td class="xl64" style="background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">152</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">abba</td> <td class="xl64">13</td> <td class="xl64">1.3E+10</td> <td class="xl64">2130077</td> <td class="xl65" style="background: rgb(255, 255, 204) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">4.3501</td> <td class="xl64">583</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">abba</td> <td class="xl64">20</td> <td class="xl64">2E+10</td> <td class="xl64">5836652</td> <td class="xl65">10.5718</td> <td class="xl64">1507</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">cred</td> <td class="xl64">9</td> <td class="xl64">9E+09</td> <td class="xl64">31692515</td> <td class="xl65">5.0462</td> <td class="xl64" style="background: rgb(204, 153, 255) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">133</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">dubba</td> <td class="xl64">2</td> <td class="xl64">2.2E+09</td> <td class="xl64">7572462</td> <td class="xl65">9.8766</td> <td class="xl64">1593</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">dubba</td> <td class="xl64">3</td> <td class="xl64">3E+09</td> <td class="xl64">11635944</td> <td class="xl65">10.6658</td> <td class="xl64">2134</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">dubba</td> <td class="xl64">3</td> <td class="xl64">3.1E+09</td> <td class="xl64">12325480</td> <td class="xl65">5.6837</td> <td class="xl64">1719</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">fudda</td> <td class="xl64">6</td> <td class="xl64">6E+09</td> <td class="xl64">9727749</td> <td class="xl65">15.1542</td> <td class="xl64">2342</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">fudda</td> <td class="xl64">11</td> <td class="xl64">1.1E+10</td> <td class="xl64">7340281</td> <td class="xl65">16.3139</td> <td class="xl64">2175</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">fudda</td> <td class="xl64">11</td> <td class="xl64">1.1E+10</td> <td class="xl64">2144402</td> <td class="xl65">5.1276</td> <td class="xl64">720</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">fudda</td> <td class="xl64">19</td> <td class="xl64">1.9E+10</td> <td class="xl64">6687251</td> <td class="xl65">9.4833</td> <td class="xl64">1132</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">fudda</td> <td class="xl64">19</td> <td class="xl64">1.9E+10</td> <td class="xl64">1523257</td> <td class="xl65" style="background: rgb(255, 255, 204) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">4.8018</td> <td class="xl64">413</td> </tr> </tbody></table>

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(--(NameRange=Name),--(ItemRange > 5),ItemRange)

assuming that column 2 (the Item field) is what you want to sum.

 

[fbogus]

Try...

=SUMPRODUCT(--(NameRange=Name),--(ItemRange > 5),ItemRange)

assuming that column 2 (the Item field) is what you want to sum.

I set the name ranges and Excel displays #NAME? error. Within the formulare the "Name" range cells are highlighted. I replace ItemRange with the named range "Item" and I see those cells highlighted.
=SUMPRODUCT(--(Name),--(Item > 5),Item)

So what is wrong with my establishing the name reference?

 

[fbogus]

Nevermind. I figured it out. Thanks for the solution.