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" wrote in message ... 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 |
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
All times are GMT +1. The time now is 08:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com