开发者

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

开发者 https://www.devze.com 2023-03-12 14:45 出处:网络
I am trying to create开发者_如何学Go 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.

I am trying to create开发者_如何学Go 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.


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)


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.


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!

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号