CONDTIONAL Sum Formula Help needed

**Mikisunshine** · 07-21-2010, 06:32 PM

I have a spreadsheet of, basically, 3 columns. One column is a category, one a debit, one a credit. I need a formula that will compare all cells in the category column and if they match, total the figures in the debit column. That formula would also be used for adding up the specific cells in the credit column too.

e.g. if a cell in column A="Rent" then add that row's cell in column B with all the other B cells that equal "Rent".

Am I making any sense at all? COUNTIF won't work and neither does SUMIF, and i'm not advanced enough to figure this one out for myself. HELP!!!!

**daddylonglegs** · 07-21-2010, 06:44 PM

If you only want to total column B then you should be able to use SUMIF, i.e.

=SUMIF(A:A,"Rent",B:B)

or do you want to add the credits and subtract the debits, in which case either use 2 SUMIFs like

=SUMIF(A:A,"Rent",C:C)-SUMIF(A:A,"Rent",B:B)

or SUMPRODUCT like

=SUMPRODUCT((A2:A100="Rent")+0,C2:C100-B2:B100)

**shg** · 07-21-2010, 06:48 PM

Welcome to the forum, Mikisunshine.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

**Mikisunshine** · 07-21-2010, 08:13 PM

Originally Posted by shg

Welcome to the forum, Mikisunshine.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

Sorry about that. I hope this is better.

**shg** · 07-21-2010, 08:36 PM

How about something simple and descriptive: Conditional Sum

**Mikisunshine** · 07-22-2010, 03:14 PM

Originally Posted by daddylonglegs

=SUMPRODUCT((A2:A100="Rent")+0,C2:C100-B2:B100)

Dear Daddy (sorry, couldn't resist)

OMG I think that's it. I only need to add up one column, i.e. Credit column for Rents, Debit column cells for Maintenance, Commissions, etc. But this seems to work. Wahoo! I just learned something new and am getting better and better at Excel every day. Gosh I love this program. And this forum! Thank you!

**TAGPGA** · 12-07-2012, 12:24 PM

Originally Posted by daddylonglegs

If you only want to total column B then you should be able to use SUMIF, i.e.

=SUMIF(A:A,"Rent",B:B)

or do you want to add the credits and subtract the debits, in which case either use 2 SUMIFs like

=SUMIF(A:A,"Rent",C:C)-SUMIF(A:A,"Rent",B:B)

or SUMPRODUCT like

=SUMPRODUCT((A2:A100="Rent")+0,C2:C100-B2:B100)

How can this formula work if you have many labels for your column A data? In this example, if you had thousands of different labels (rent, utilities, etc).

In my context, I have a similar problem, but individually setting up formulas for each label is impractical:

In column A, I have thousands of different customer account numbers. Each account has multiple transactions.
In column B, I have the dollar value for the transaction. Each row represents a separate transaction.

I want to find out the total number of dollars spent by each account for the whole year.

I need excel to find all rows with each account number (column A), and then add the values in Column B.

Thanks!

**arlu1201** · 12-07-2012, 02:18 PM

TAGPGA,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.