Office spreadsheet question for you lurkers

[Concillian]

I'm sure there's a function in Excel that will do what you need without using a macro, but I do not memorize the functions in Excel. Instead I know how to make macros, so it's easiest to help by just making a macro to do what you want to do.

I'm assuming you have something that looks like this"

Code:

Food   10.50
Toys   8.48
Gas  38.50
Food   42.00

and want to sum only the food ones. As I said, I'm sure that there's a function in excel that will do this, I just don't know what it is. My deal is learn how to make a macro and you can create anything you need without trying to figure out some bogus syntax... though you do have to figure out VBA, so that logic may have a flaw in it.

Code:

Sub Food_macro

dim i as integer
dim FoodTotal as single

[A1].select  'set this to the upper left cell

FoodTotal = 0
i=0

  do until ActiveCell.offset(i,0).value = ""
   if ActiveCell.Offset(i,0).value = "Food"
      FoodTotal = FoodTotal + Activecell.Offset(i,1).Value
   else
      
   end if
  loop
[G1].value = FoodTotal

end sub

If you want to get fancy, you set all your varaibles to sum at the same time

Code:

Sub Food_macro

dim i as integer
dim FoodTotal as single
dim GasTotal as single
dim ToysTotal as single
dim PotTotal as single

[A1].select  'set this to the upper left cell

FoodTotal = 0
i=0

  do until ActiveCell.offset(i,0).value = ""
   Select Case ActiveCell.Offset(i,0).value
      Case is = "Food"
         FoodTotal = FoodTotal + Activecell.Offset(i,1).value
      Case is = "Toys"
         ToysTotal = ToysTotal + Activecell.Offset(i,1).value
      Case is = "Gas"
         GasTotal = GasTotal + Activecell.Offset(i,1).value
      Case is = "Pot"
         PotTotal = ToysTotal + Activecell.Offset(i,1).value
   end Select
   i=i+1

   Loop

   [G1].value = FoodTotal
   [G2].value = ToysTotal
'  etc...

End Sub

If you need to change sheets, the syntax is
Sheets("Finance").select

It might be a good idea to put this into the macro so you don't go editing the wrong sheet inadvertently.

Also, the loop will continue until it finds a cell with nothing in it, so if you have a different ending condition (perhaps a cell that says "Total" or something) then replace the "" with "<unique value of the cell>". You can also use any other cell a fixed offset from it by using Activecell.Offset(i+x,y).

Someone else probably replied with a much simpler solution by now, but I'm gonna go ahead and post this anyway.