Sum a Range - Dependent on other cell value

[arizzo]

Hi there. First post for me. My problem is pretty simple (I think) but I am still having some trouble.

If you look at the table below. This spreadsheet tracks the number of pieces of fruit sold. I enter the fruit name and quantity. Below I would like it to automatically keep track of the total amount of each fruit sold.

So, for example, the 'Total' value for the Category 'Apples' should read 14, for Banana's, 28, etc...

What I think I need to do is enter a formula in each of the 'Categories -> Total' cells that would loop through the Range of 'Quantity' and Sum them if the 'Fruit Name' equals the corresponding 'Category'.

Therefore, as per the Title of my post, the Sum formula will be dependent on both the Category and the Fruit name. Here is my thought process in an 'algorithm' format.

For Start of Range to End of Range
If Category.Cell.Value = Fruit Name.Cell.Value
Sum = Sum + Sum
Else
Go to next row
End If
Next

Hopefully this makes sense.

<table style="border-collapse: collapse; width: 169pt;" border="0" cellpadding="0" cellspacing="0" width="225"><col style="width: 60pt;" width="80"> <col style="width: 61pt;" width="81"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 60pt;" height="20" width="80">
</td> <td class="xl65" style="width: 61pt;" width="81">Fruit Name</td> <td class="xl65" style="width: 48pt;" width="64"> Quantity</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Apple</td> <td align="right">2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Orange</td> <td align="right">5</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Banana</td> <td align="right">3</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Pear</td> <td align="right">7</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Apple</td> <td align="right">8</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Apple</td> <td align="right">4</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Pear</td> <td align="right">9</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Banana</td> <td align="right">10</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Banana</td> <td align="right">15</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">Category</td> <td class="xl66">Total</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Apple</td> <td class="xl67">?</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Banana</td> <td class="xl67">?</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Orange</td> <td class="xl67">?</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Pear</td> <td class="xl67">?</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Plum</td> <td class="xl67">?</td> <td>
</td> </tr> </tbody></table>
Thanks in advance for any suggestions you may have.

 

[Scott Huish]

A Pivot Table would be perfect for this:

http://peltiertech.com/Excel/Pivots/pivottables.htm

 

[dcamos]

You could use the sumproduct formula
=sumproduct((Category Range = "Category")*(Fruit Range = "Fruit")*(Range with Values))

 

[arizzo]

Thanks. I will take a look at that.

 

[arizzo]

Hi there. The Pivot table seems to work great. One thing I didn't mention is that I do have another column called Date (which is simply the date the items are entered). Because of this I was using automatic Subtotals. It seems as though Pivot Tables don't like Subtotals. Is there anyway around this. If it is too complicated it isn't a huge deal as I can get away with out the Subtotals.

thanks.