Excel - 如果通过检查同一表中的其他列来满足条件，则为Sum列 [英] Excel - Sum column if condition is met by checking other column in same table

问题描述

我正在尝试创建一个电子表格，用于一个小型零售店。

我有一本工作簿，其中包含了一张表上的费用数字和另一张表上的收入数字。

我们可以说两张单之间的共同点是月份。

我想写一个功能，只会选择指定月份的费用。

像 -

  = SUM（IF（Table4 [Month ] =1月，表4 [金额]）
 
 //我想要给定表的所有费用的总和只有
 //那些月份等于
 $ b  

我尝试使用上述方法，但失败了。

解决方案

这应该有效，但有一点小技巧。输入公式后，您需要按Enter键Ctrl + Shift。当你这样做，你会看到公式栏围绕你的公式卷曲。这被称为数组公式。

例如，如果月份在单元格 A2：A100 中，在单元格 B2：B100 中，您的公式将看起来像 {= SUM（If（A2：A100 =January，B2：B100）） } 。你也不会像 = SUM（（A2：A100 =1月）这样做， ）* B2：B100）。您仍然需要使用诀窍才能使其正常工作。

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.

解决方案

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.

这篇关于Excel - 如果通过检查同一表中的其他列来满足条件，则为Sum列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！