Excel - Sum column if condition is met by checking other column in same table

Question

I am trying to create spreadsheet to use in a small retail shop.

I have a workbook which contains expenses figures on a sheet and income figure on another sheet.

We can say that the common ground between the both sheets are the month.

I would like to write a function, which will only select those expenses of a specified month.

Something like -

=SUM(IF( Table4[Month]="January", Table4[Amount])) 

// I want the sum of all expenses of a given table for only 
// those months which are january etc.

I tried using the above, but it failed.

score 64 · Answer 1 · edited Jun 07 '13 at 15:41

64

Actually a more refined solution is use the build-in function sumif, this function does exactly what you need, will only sum those expenses of a specified month.

example

=SUMIF(A2:A100,"=January",B2:B100)

edited Jun 07 '13 at 15:41

Renaud

16,073
6
81
79

answered Jun 10 '11 at 20:03

Alen

1,040
1
8
13

1

@Don Pratt, Did not appear how call an array formula, but I understand is hold down **shift** while press **enter**, right?. – Alen Jun 10 '11 at 20:04
To call an array formula you need to press CTRL+SHIFT+ENTER. – Jubbles Jun 10 '11 at 20:23
Good point, @Alen. Thanks for the reminder. SUMIF() works great as long as you only have one criteria as in the example. Either of the array formulas can be expanded to handle multiple criteria. Excel 2007 added the SUMIFS() function to deal with this, but old patterns die hard ;) – Don Pratt Jun 10 '11 at 21:45
1

A2:A100 is the range to be checked by the condition "=January". B2:B100 is the range to be summed. – Nathan Jan 08 '14 at 23:45

score 19 · Accepted Answer · edited Jul 14 '15 at 13:45

19

This should work, but there is a little trick. After you enter the formula, you need to hold down Ctrl+Shift while you press Enter. When you do, you'll see that the formula bar has curly-braces around your formula. This is called an array formula.

For example, if the Months are in cells A2:A100 and the amounts are in cells B2:B100, your formula would look like {=SUM(If(A2:A100="January",B2:B100))}. You don't actually type the curly-braces though.

You could also do something like =SUM((A2:A100="January")*B2:B100). You'd still need to use the trick to get it to work correctly.

edited Jul 14 '15 at 13:45

Iwan Aucamp

1,469
20
21

answered Jun 10 '11 at 16:58

Don Pratt

404
3
6

Thanks alot!, the first option did not work for me, but I tried it the second style and worked like a charm! :D – Kunal Jun 10 '11 at 17:40
2

15 Years in excel never heard of the array formula. How do you get know that?! +1 is not enough for you. – Emad Jan 09 '17 at 09:32
Clever answer but @Alen's solution below is more elegant – Peter Chaula Nov 13 '17 at 10:19

score 0 · Answer 3 · edited Mar 05 '16 at 21:35

0

SUMIF didn't worked for me, had to use SUMIFS.

=SUMIFS(TableAmount,TableMonth,"January")

TableAmount is the table to sum the values, TableMonth the table where we search the condition and January, of course, the condition to meet.

Hope this can help someone!

edited Mar 05 '16 at 21:35

Tunaki

132,869
46
340
423

answered Mar 05 '16 at 21:30

Luís Baptista Lourenço

65
6

Excel - Sum column if condition is met by checking other column in same table

3 Answers3

Linked