按日期（A列）过滤行并将总和（C列）写入另一个工作表中的字段 [英] Filter rows by date (A column) and write sum (C column) to field in another sheet

问题描述

我有两张 Google Spreadsheet 文档。第一个列有月份，收入，费用和利润（分别为A，B，C和D），每行费用值应根据过滤器（我认为）从另一张名为费用的表中计算。

费用表中包含日期，名称和价格。因此，我需要编写公式来过滤特定月份的费用，将这些行中的所有价格相加并将该值写入C列（row for在第一张纸上）。

您可以查看这个例子并请帮我解决这个问题。

解决方案

这是一个可以填充的公式它在F2）：
$ b

= ArrayFormula（SUMIF（TEXT（Expenses！A $ 2：A;MMMM）; A2; Expenses！C这是一个公式，它会自动填充列中的结果（我已经在G2中输入了它）：





（A2（A））; SUMIF（TEXT（Expenses！A2：A;MMMM）; A2：A;费用！C2：C）; IFERROR（1/0）））

此公式将考虑年份;我已经在J2中输入了它，在I2中引用了每月的字符串：I：
$ b = ArrayFormula（IF（LEN（I2：I）; SUMIF（TEXT（Expenses！A2：A;MMMM yyyy）; I2：I; Expenses！C2：C）; IFERROR（1/0）））

I have two sheets in Google Spreadsheet document. The first one has columns Month, Income, Expense and Profit (A,B,C,D respectively) and in each row expense value should be calculated based from filter (I think) from another sheet called Expenses.

The Expenses sheet contains columns Date, Name and Price.

So, what I need is to write formula to filter only expenses for specific month, to sum all prices in those rows and to write that value to C column (row for that month) in first sheet.

You can see this example and please help me out to solve this.

解决方案

This is a formula that can be filled down (I have entered it in F2):

=ArrayFormula(SUMIF(TEXT(Expenses!A$2:A;"MMMM");A2;Expenses!C$2:C))

This is a formula that will automatically populate results down the column (I have entered it in G2):

=ArrayFormula(IF(LEN(A2:A);SUMIF(TEXT(Expenses!A2:A;"MMMM");A2:A;Expenses!C2:C);IFERROR(1/0)))

And this formula will take the year into consideration; I have entered it in J2, referencing month-year strings in I2:I:

=ArrayFormula(IF(LEN(I2:I);SUMIF(TEXT(Expenses!A2:A;"MMMM yyyy");I2:I;Expenses!C2:C);IFERROR(1/0)))

这篇关于按日期（A列）过滤行并将总和（C列）写入另一个工作表中的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！