adding total
Posted by Walter on November 17, 2000 6:38 PM
Hi,
I like to create a Macro in Excel to make some adding up tasks easier. I am new to this and hope it is not too complicated.
I get an excel spreadsheet once a week with items sold. Some of the values have to be added up like local beers and imported beers into one Total. They don't always appear on the same line as items are added and taken off week by week. The Headings like Local Beers or Imported Wines stay the same.
I have an example at: http://www.multiline.com.au/~wje/macro.html
You can see there how it would change from week 1 to week 2.
Thanks for your help
Walter
Posted by Ben O. on November 21, 2000 7:34 AM
This is a task that's obvious for humans, but difficult to explain to a computer. I can't think of an easy way to do it that doesn't involve changing the way the spreadsheet is arranged. Do you have any control over that? It would be simple if there were separate columns for the beer/wine name and the category, like this:
Local Beer - Swan
Local Beer - Emu Export
Imprt Beer - Heinken
Imprt Beer - Carlsberg
Local Wine - Mossworrd
Local Wine - Westfield
etc.
Sorry I couldn't be of more help. I'm thirsty now for some reason.
-Ben
Posted by Ivan Moala on November 25, 2000 5:46 AM
Local Beer - Swan
Walter
In your example I noted that the Local beers and
wines etc headings were static, your Total Beers
was also in the same row and that the totals
for these had no heading.
If so then a simple Sumif formula will get the
results you need.
eg for Beer Totals
=SUMIF($A5:$A30,"<>",B5:B30)
What this is doing is looking up the range A5:A30
which could be the column range of your Beers as
the sub totals have nothing next to them then it skips
this and only adds the ones with something in them.
same deal with the Wine Totals;
=SUMIF($A33:$A58,"<>",B33:B58)
This assumes that the Beers are in Ranges given
and wines are in the ranges given.....from your
example they appear to be in a set range, the only
diff is some weeks the aree more or less beers.
HTH
Ivan
