SUM condition on one set of columns where another includes certain values

"Not particularly elegant, but:

=SUMPRODUCT((RIGHT(A2:A7,5)=""total"")*1,B2:B7)

For some reason you can't use A:A or B:B in it..."

"Are the total rows actually calculated as totals, or are they just flat data imported from somewhere along with the breakdown values?

If they are calculated, then a simple trick would be this:
Don't sum the totals using SUM, use SUBTOTAL (using option 9 for the sum type of subtotal).
so eg in B2 you would have =sSUBTOTAL(9,B1:B1), in B6 =SUBTOTAL(9,B3:B5) and so on.

Now you can simply use another SUBTOTAL function to add up the whole of column B, and it will ignore the other subtotals completely. This is in effect the reverse of adding up the totals, it adds up only the original data points, but in some ways that is more robust if you had an incorrect total line due to an incorrect formula.
This technique works in all versions of Excel which have the subtotal function and is discussed briefly here because of a related change in the status bar in Excel 2010: A couple of quick Excel 2010 discoveries"