summing an array by product category

I need help writing a macro that will look down the first column and will add
up the prices of whatever rows that have the same category.

Example:
category product price
pets horse 100
pets dog 50
food banana .50
food hamburger 1.50

pets subtotal: 150
food subtotal: 2.00

Thank you very much.

BLW

Hi
It is possible to do this without a macro using SUMPRODUCT or SUMIF. If you
don't specifically need a macro, post back and we'll respond for you!

Andy.

"BLW" <BLW@discussions.microsoft.com> wrote in message
news:D64C9509-2D24-4AD9-AEEE-BE896E02039E@microsoft.com...
>I need help writing a macro that will look down the first column and will
>add
> up the prices of whatever rows that have the same category.
>
> Example:
> category product price
> pets horse 100
> pets dog 50
> food banana .50
> food hamburger 1.50
>
> pets subtotal: 150
> food subtotal: 2.00
>
> Thank you very much.
>
> BLW
>
>

Andy, I don't specifically need to use a macro so long as I can continually
select more products and the subtotals change with the new selection. In any
case, I would like to see how the two functions you described are use.
Thanks a lot

"Andy B" wrote:

> Hi
> It is possible to do this without a macro using SUMPRODUCT or SUMIF. If you
> don't specifically need a macro, post back and we'll respond for you!
>
> Andy.
>
> "BLW" <BLW@discussions.microsoft.com> wrote in message
> news:D64C9509-2D24-4AD9-AEEE-BE896E02039E@microsoft.com...
> >I need help writing a macro that will look down the first column and will
> >add
> > up the prices of whatever rows that have the same category.
> >
> > Example:
> > category product price
> > pets horse 100
> > pets dog 50
> > food banana .50
> > food hamburger 1.50
> >
> > pets subtotal: 150
> > food subtotal: 2.00
> >
> > Thank you very much.
> >
> > BLW
> >
> >
>
>
>

=SUMPRODUCT(--(A1:A100="pets"),C1:C100)

or

=SUMIF(A1:A100,"pets",C1:C100)

--
HTH

Bob Phillips

"BLW" <BLW@discussions.microsoft.com> wrote in message
news:9C2704C7-9F3A-4CA5-81D6-03E2833212E3@microsoft.com...
> Andy, I don't specifically need to use a macro so long as I can
continually
> select more products and the subtotals change with the new selection. In
any
> case, I would like to see how the two functions you described are use.
> Thanks a lot
>
> "Andy B" wrote:
>
> > Hi
> > It is possible to do this without a macro using SUMPRODUCT or SUMIF. If
you
> > don't specifically need a macro, post back and we'll respond for you!
> >
> > Andy.
> >
> > "BLW" <BLW@discussions.microsoft.com> wrote in message
> > news:D64C9509-2D24-4AD9-AEEE-BE896E02039E@microsoft.com...
> > >I need help writing a macro that will look down the first column and
will
> > >add
> > > up the prices of whatever rows that have the same category.
> > >
> > > Example:
> > > category product price
> > > pets horse 100
> > > pets dog 50
> > > food banana .50
> > > food hamburger 1.50
> > >
> > > pets subtotal: 150
> > > food subtotal: 2.00
> > >
> > > Thank you very much.
> > >
> > > BLW
> > >
> > >
> >
> >
> >

oTry a pivot table, it will give you this, in the layout drag the category
header in the row field and the price to the data field


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



Regards,

Peo Sjoblom

"BLW" wrote:

> I need help writing a macro that will look down the first column and will add
> up the prices of whatever rows that have the same category.
>
> Example:
> category product price
> pets horse 100
> pets dog 50
> food banana .50
> food hamburger 1.50
>
> pets subtotal: 150
> food subtotal: 2.00
>
> Thank you very much.
>
> BLW
>
>