Sum function based off another cell

[jeffv]

I have a spreadsheet of customers and their most recent statement balance. There is also another column with codes we use to track customers. I would like to have a sum formula that adds all of the balances in column F, but leave out the balances that are coded B0, B1, B3 and B7, which are located in column B. Could someone help me understand how I need to do the formula?

Thanks.

[Pete_UK]

You could use a formula like this:

=SUM(F:F) - SUMIF(B:B,{"B0","B1","B3","B7"},F:F)

Hope this helps.

Pete

[jeffv]

Thanks. I wanted to get a bit more specific and I tweaked your formula a bit and now it doesn't work. I don't need the entire B and F columns, just a range. I also forgot about a couple of more codes, so I added those. Did I mess something up or why isn't it working?

=SUM(F9:F705) - SUMIF(B9:B705,{"B0","B1","B2","B3","B4","B7","BD"},F9:F705)

[Pepe Le Mokko]

What do you mean by " it doesn't work"?

[jeffv]

Sorry, I should have elaborated. When I tweaked the formula to the one I posted, it just displays the formula in the cell, it doesn't do any calculation.

[newdoverman]

I can't get it to work either.

I did get this to work:

Please Login or Register  to view this content.

The B* is a criteria for everything that starts with B

[jeffv]

I get the same result with that formula, too. When I paste it and hit Enter, it just displays the formula.

Edit:
One thing I didn't think of was, this is a spreadsheet that I converted from PDF. I converted it to a txt file and then imported it using the Data tab. Could that be why nothing is working?

[newdoverman]

It would appear that your worksheet is TEXT so formulae will only show as formulae and not do calculations.

[jeffv]

Goodnees gracious, I'm a moron. Now the formula is working perfectly. Thank you so much for your help.

Just for clarification, the number that's returned is the total amount of balances that have a code that begins with B, correct?

[Pete_UK]

Just for clarification, the number that's returned is the total amount of balances that have a code that begins with B, correct?

No. This part of the formula that Ron suggested:

SUMIF(B9:B705,"B*",F9:F705)

gives you that, but it is subtracted from the sum of ALL values in the range, so the result is the opposite of what you stated.

Hope this helps.

Pete

[newdoverman]

=SUM(F9:F705) - SUMIF(B9:B705,"B*",F9:F705)

=SUM(F9:F705) This part sums the total for the column

-SUMIF(B9:B705,"B*",F9:F705) This part sums everything that begins with B and in the formula, subtracts this amount from the total thus giving you the sum of everything minus the codes that start with B.