更新：尝试通过 Google 表格中的多个条件从多行中提取值
好吧——我第一次在这个问题上的逻辑是错误的。我试图使用 SUMIFS，但进一步的研究表明我需要使用 SUMPRODUCT。
我正在尝试在 Google 表格中运行计算，该计算将根据匹配的两行值对单列的值进行求和：匹配“目标”表的第一个单元格中的月份以及“数据”表中的特定值。
此处的示例表
https://docs.google.com/spreadsheets/d/1EpRDGHqrXWpjGxeDlofy5LitI1BdZpSG6V0fbBwrIoE/edit?usp=sharing
例如......
我的目标表：COL A、COL B
| 日期 | 支出 |
|---|---|
| 01/01/2022 | 此处计算 |
| 02/01/2022 | 此处计算 |
我的数据表：COL A、COL B、COL C
| 日期 | 名称 | 美元 |
|---|---|---|
| 01/28/2022 | 账单 | $.50 |
| 01 /29/2022 | 特德 | $1.00 |
| 01/29/2022 | Ted | $1.50 |
| 01/30/2022 | 账单 | $2.00 |
| 02/01/2022 | Ted | $3.00 |
| 02/01/2022 | 账单 | $4.00 |
| 02/01/2022 | Ted | $5.00 |
| 02/02/2022 | 账单 | $6.00 |
| 02/02/2022 | Ted | $7.00 |
| 02/02/2022 | Bill | $8.00 |
如果 COL A 中的日期在同一个月且 COL B 中的名称值匹配，我想将美元总和从数据表 C 列提取到目标表 B 列。例如，Ted 一月的总计、Ted 二月的总计。
因此，在我的目标表中，如果我匹配 TED，我会得到
| DATE | SPEND |
|---|---|
| 01/01/2022 | $2.50 |
| 02/01/2022 | $15.00 |
现在，我不得不将日期范围硬编码到 SUMPRODUCT 方程中。 .如果我不尝试匹配 COL B 中的名称（请参阅目标表工作表中的单元格 B5），它会起作用，但是当我添加它时条件，方程中断（请参阅“目标表”工作表中的单元格 B4）
以下是有效的方程：
=SUMPRODUCT(('DATA TABLE'!$C$2:$C),--(DATE(YEAR('DATA TABLE'! $A$2:$A),月份('数据TABLE'!$A$2:$A),1)>=DATE(2022,2,1)),--(DATE(YEAR('数据表'!$A$2:$A),MONTH('数据TABLE'!$A$2:$A),1)<=DATE(2022,2,28)))
这是给出公式解析错误的方程式。
=SUMPRODUCT(('数据表'!$C$2:$C),--(DATE(YEAR('数据表'!$A$2:$A),MONTH('数据表'!$A$2:$A ),1)>=DATE(2022,2,1)),--(DATE(YEAR('数据表'!$A$2:$A),MONTH('数据TABLE'!$A$2:$A),1)<=DATE(2022,2,28)),--('DATA TABLE'!$B$2:$B,="Ted"))
感谢您的任何对此提供帮助！
OK - I had my logic wrong on this the first time around. I was trying to use SUMIFS but further research makes it look like I need to use SUMPRODUCT.
I am trying to run a calculation in Google Sheets that will sum the value of a single column based on matching two row values: matching the MONTH in the first cell of my ‘target’ table, and specific values in the ‘data’ table.
SAMPLE TABLE HERE
https://docs.google.com/spreadsheets/d/1EpRDGHqrXWpjGxeDlofy5LitI1BdZpSG6V0fbBwrIoE/edit?usp=sharing
FOR INSTANCE….
MY TARGET TABLE: COL A, COL B
| DATE | SPEND |
|---|---|
| 01/01/2022 | CALC HERE |
| 02/01/2022 | CALC HERE |
MY DATA TABLE: COL A,COL B,COL C
| DATE | NAME | DOLLARS |
|---|---|---|
| 01/28/2022 | Bill | $.50 |
| 01/29/2022 | Ted | $1.00 |
| 01/29/2022 | Ted | $1.50 |
| 01/30/2022 | Bill | $2.00 |
| 02/01/2022 | Ted | $3.00 |
| 02/01/2022 | Bill | $4.00 |
| 02/01/2022 | Ted | $5.00 |
| 02/02/2022 | Bill | $6.00 |
| 02/02/2022 | Ted | $7.00 |
| 02/02/2022 | Bill | $8.00 |
I want to pull the total sum of DOLLARS from DATA TABLE Col C into TARGET TABLE Col B if the dates in COL A are in the same month AND the NAME VALUE in COL B Match. For instance, Ted's Total in January, Ted's Total in February.
So in my TARGET TABLE if I were matching for TED, I would get
| DATE | SPEND |
|---|---|
| 01/01/2022 | $2.50 |
| 02/01/2022 | $15.00 |
For now I've had to resort to hard-coding a date range into a SUMPRODUCT equation...it works if I don't try to match the NAME in COL B (see CELL B5 in TARGET TABLE worksheet), but when I add that criteria, the equation breaks (see CELL B4 in TARGET TABLE worksheet)
Here is the equation that works:
=SUMPRODUCT(('DATA TABLE'!$C$2:$C),--(DATE(YEAR('DATA TABLE'!$A$2:$A),MONTH('DATA TABLE'!$A$2:$A),1)>=DATE(2022,2,1)),--(DATE(YEAR('DATA TABLE'!$A$2:$A),MONTH('DATA TABLE'!$A$2:$A),1)<=DATE(2022,2,28)))
Here is the equation that gives me a FORMULA PARSE ERROR.
=SUMPRODUCT(('DATA TABLE'!$C$2:$C),--(DATE(YEAR('DATA TABLE'!$A$2:$A),MONTH('DATA TABLE'!$A$2:$A),1)>=DATE(2022,2,1)),--(DATE(YEAR('DATA TABLE'!$A$2:$A),MONTH('DATA TABLE'!$A$2:$A),1)<=DATE(2022,2,28)),--('DATA TABLE'!$B$2:$B,="Ted"))
Thanks for any assistance with this!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
最好不要用标题、帖子详细信息、标签或评论来“引导证人”告诉人们如何解决问题（例如，
SUMIFS或SUMPRODUCT等） 。相反，只需描述您正在尝试解决的问题，并提供易于访问的数据和所需的结果。也就是说，我在 B1 中添加了一个包含以下公式的新工作表（“Erik Help”）：
=ArrayFormula({"SPEND";IF(A2:A="",,IFERROR(VLOOKUP(A2: A,QUERY({'数据表'!A2:C},"选择 Col1, SUM(Col3) WHERE Col2 ='Ted' GROUP BY Col1"),2,FALSE),0))})此单个公式将生成标题（如果需要，可以在公式本身内更改）和所有行的所有结果。正如我离开公式时的情况一样，所有行结果都是相同的，只是因为您在 A 列的所有行中输入了相同的日期。更改 A 列中的内容，公式将检索该日期和“Ted”的结果B 列。
在这里，您可以看到既没有使用
SUMIF也没有使用SUMPRODUCT。相反，我使用QUERY创建了一个参考表，该表仅包含“Ted”的数据，然后我使用VLOOKUP按每行的日期搜索该表（因此数组公式）。It's best not to "lead the witness" with titles, post details, tags or comments that tell people how to solve the issue (e.g.,
SUMIFSorSUMPRODUCT, etc.). Instead, just describe the issue you are trying to solve and provide easily accessible data with desired outcome.That said, I've added a new sheet ("Erik Help") with the following formula in B1:
=ArrayFormula({"SPEND";IF(A2:A="",,IFERROR(VLOOKUP(A2:A,QUERY({'DATA TABLE'!A2:C},"Select Col1, SUM(Col3) WHERE Col2 ='Ted' GROUP BY Col1"),2,FALSE),0))})This single formula will produce the header (which can be changed within the formula itself if needed) and all results for all rows. As things were when I left the formula, all row results were the same only because you had the same date entered in all rows of Col A. Change what is in Col A and the formula will retrieve results for that date and "Ted" in Col B.
Here, you see that neither
SUMIFnorSUMPRODUCTwas used. Instead, I created a reference table withQUERYthat is written to only contain data for "Ted", and then I usedVLOOKUPto search that table by date per row (henceArrayFormula).