Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an Excel spreadsheet that looks like the following:

# |A           |B
-----------------------------
1 |Expenses    |Category
2 |$5          |Food
3 |$10         |Entertainment
4 |$10         |Haircut

What I want is to create a section that lists the sum for each category. Something that is organized like this:

# |H            |I
-----------------------------
1 |Category     |Total Expenses
2 |Food         |
3 |Entertainment|
4 |Other        |

Summing up the categories for Food and Enterainment were pretty easy. I used the SUMIF function to check the range of the B "Category" column against a cell in the H "Category" column, which added up the values if they matched. For instance, "I2" had a formula just like this:

=SUMIF(B$2:B$999, H2, A$2:A$99)

However, I'm having a really hard time setting up the "Other" category. What I want is for it to display any values that have a category that does not show up in H column (for instance, "Haircut"). I tried doing the following:

=SUMIF(B$2:B$999, NOT(OR(H2,H3)), A$2:A$99)

But that doesn't seem to capture any cells. Ultimately, I want for the criteria section of SUMIF to capture cells that do not contain the values found in a range of other cells.

EDIT: This is for a spreadsheet that is for a client that is not very computer saavy, so any solution should ideally be one that puts very little burden on an end user (e.g. doesn't require them to understand that macros are okay). Also the full spreadsheet includes a date column, so sorting by category won't work.

share|improve this question

4 Answers 4

up vote 1 down vote accepted

SUMIFS allows you to specify more than one condition (in Excel 2007 or later) so you could use this formula

=SUMIFS(A:A,B:B,"<>"&H2,B:B,"<>"&H3)

That will sum column A where the corresponding column B value is not equal to H2 AND not equal to H3

If you have a longer list of items to exclude then that way will make for a long formula so another way to exclude a range would be using SUMPRODUCT like this

=SUMPRODUCT(ISNA(MATCH(B2:B100,H2:H5,0))+0,A2:A100)

That will sum A2:A100 when the corresponding column B value doesn't match any of the values in H2:H5

share|improve this answer
    
I've changed this to the accepted answer because it directly answers the specific question I had about SUMIF, although there might be better alternatives to accomplish the same result. –  Thunderforge Jul 26 '13 at 18:16

What you need is a Pivot Table.

Select one of the cells of your table, let's say A1. Create a Pivot Table by clicking: Insert > Pivot Table. Click ok.

At this point you will have a Pivot Table created in a new sheet. To calculate what you want drag and drop the Category from the filed list, in the box at the right side of the table, to the Row Labels, then, drag and drop Expenses to the ∑ Values.

EDIT

As you asked in your question, the Pivot Table doesn't need to be created in a separate sheet. Instead of clicking OK when the Pivot Table prompts a dialog, you can specify where you want to create your Pivot Table.

Also, remember to refresh the table whenever it's contents are modified, by right-clicking the Pivot Table and then clicking Refresh.

EDIT 2

I'm sorry, I complete misread your question, you were able to calculate the sum of the Categories, so you don't need a Pivot Table.. What you need is to sum the others categories, my suggest:

Instead of try to sum all the categories that weren't summed, why don't you sum the total from A1:A999 and subtract all the summed values in the I column?

Let's suppose your Other field is at H4:

=SUM(A$1:A$999) - SUM(I$2:I$3)

share|improve this answer
    
Is there any way to make it auto-refresh? I'm creating this for others who have never used pivot tables and I'm not sure that they will remember to hit Refresh every time they update. –  Thunderforge Jul 25 '13 at 18:59
    
Instructions here tell me that I can make it work with a macro, but if clients not remembering to refresh is bad enough, I think they'll get freaked out by a macro security warning. I'm afraid I can't accept this solution if this is the case. –  Thunderforge Jul 25 '13 at 19:15
    
Well, it is a Macro I was looking for, actually. So, Macro isn't a good solution either? What about refresh when open the file? –  Math Jul 25 '13 at 19:18
    
A macro would be fine if I were using it, but it's for a client. I did see "refresh when open the file", which I've already checked, but I'd ideally like something that updates instantly with the value totals. I guess I can't have it all and I'll have to compromise on something? –  Thunderforge Jul 25 '13 at 23:08
    
What about my Edit 2? –  Math Jul 25 '13 at 23:52

A straightforward way to do this is to sort the data by category and use SUBTOTAL. You can either use the function SUBTOTAL repeatedly or (the easier way) highlight your sorted A and B columns and use Subtotal from Outline in the Data Ribbon (I forget which menu it's under in earlier versions of Excel). That will automatically insert subtotals whenever the category changes, include a grand total, and set up outline processing so you can collapse or expand the subtotals.

Subtotal is very handy for this kind of thing. It supports multiple layers of totals, so you could have separate subtotals by department, by category in department, etc.

share|improve this answer
    
Unfortunately, sorting the data by category isn't an option, otherwise I would probably just be able to use SUM. The full table includes a column for dates. –  Thunderforge Jul 25 '13 at 23:05
    
Sort by category, use automatic subtotal. Paste the subtotal lines as values, then resort back by date. If that works for you, save it in a macro. –  mpez0 Aug 14 '13 at 17:20

How about setting I4 to =SUM(A$2:A$99) - SUM(I$2:I3) –– all the data in Column B minus the (sub) totals you’ve already accumulated in Column I.  If you need to be able to add (and/or delete) categories with a minimum of fuss and bother, look at

=SUM(A$2:A$99) - SUM(OFFSET(I$2,0,0,ROW()-2,1))

where the ROW() function figures out what row it is in (i.e., what row is the “Other” row).  Subtract 2 for Row 1 and the current row; for example, if you have eight summed categories, in Cells H2:H9, then Other is in H10, and you want to sum I2:I9, which is eight (10-2) rows.

share|improve this answer
    
Thanks for realizing that there was a simpler solution than what I was asking for :-) –  Thunderforge Jul 26 '13 at 1:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.