SUM function with complications. Need help with formula.

[kbdavis]

Let's see if anyone out there could help me out with this problem...

Let's say I was needing to sum up the total in column B.. Easy enough, huh? Well, if it were only that simple (which I am sure it is for most of the people reading this or I wouldn't be here).

Let's say the only cells in column B i wanted to add up were cells that contained a specific "Client Code" in column A.

Example:

|    A    |    B    || ABC100  | $500.00 || ZZZ900  | $100.00 || ABC100  | $200.00 || XYZ350  | $ 50.00 |

I am needing it to automatically add up all the #s in "B" that has the client code of ABC100. So that would be B1,B3, and to ignore the rest. Is there a formula that would allow me to do this?

Thanks in advance!

Edited August 6, 2013 by kbdavis

[allen2]

To do it you'll need a macro most likely:

- first you need to get unique value of col A

- for each unique value, find all line (j) of col A containing it and add the cell (j,B )

Another way is using the subtotal feature after sorting on Col A.

Edited August 6, 2013 by allen2

[Yzöwl]

It's a long time since I used excel, but it sounds to me as if you need to use the SUMIF function.

Try this, based on your example and assuming ROW1 begins ABC100 and the currency symbols are added only as part of the cell format:

=SUMIF(A1:A4,"ABC100",B1:B4)

[kbdavis]

It's a long time since I used excel, but it sounds to me as if you need to use the SUMIF function.

Try this, based on your example and assuming ROW1 begins ABC100 and the currency symbols are added only as part of the cell format:

=SUMIF(A1:A4,"ABC100",B1:B4)

example.png

That worked Perfectly!

Thank you very much.

I did make a very slight modification to the formula mentioned, using =SUMIF(A:A,"ABC100",B: B)

That helped out a lot!

Edited August 7, 2013 by Tripredacus
disabled emoticons