Using IF formula for multiple cells

[bdr6]

Hi Everyone,

I am trying to write a formula to recognize certain data and add the sum of all cells that meet the criteria.

I have a number of different expense groupings such as accommodation, deck, propulsion etc. I would like to make a formula to go through each row and if B=Accommodation then it will add the expense associated with that row to the accommodation total on a new worksheet but if not then it will not add the cell total ($ amount for each category is listed in Column H). Then I can make a line item for each type of expenses in the new workbook with a total that only picks up the cells in that category. I do not want to sort by Column B and then just subtotal and I would like it to automatically recalculate in the new worksheet if I insert a new row into the original worksheet. The formula that I have right now is:

=IF(B2:B203=Accommodation,SUM(H2:H203),0)

but that does not go line by line, just adds to total of all the cells in Column H if any cell in Column B=Accommodation

I can get it to work for a single line with the formula

=IF (B2=Accommodation,H2,0)

Is there a way to do this for all cells from B2-B203 without typing a formula for each row?

Hope that all made sense.

Thanks in advance.

Bonny

 

[VoG]

Try

=SUMIF(B2:B203,"Accommodation","H2:H203)

 

[bdr6]

Thank you! That worked. I have been fiddling with it all morning!