Google电子表格：满足两个条件的每一行的总和 [英] Google Spreadsheet: Sum of every row where two conditions are met

问题描述

我有两个电子表格。第一个是产品列表，第二个是销售列表。

产品电子表格包括四列：SKU，货件编号，已购买数量和剩余数量。

      A          ;   B                    C   &NBSP ;                     D

  ;     SKU          装运ID   购买数量   剩余数量

1    20121    X2992             40                         38

2    20121    X8483             40                        40

3    71662     X2242             40                        39

销售电子表格包含三列：SKU 货件编号和已售数量。

      A             B                    C ^，点击
      SKU      装运ID   数量已售

1 &    20121    X2992             1

2    71662     X2242             1

3    20121          我想要剩余数量被计算出来，            1

< 。此列的每一行应采用自己的行中的已购买数量，并从销售电子表格中扣除SKU和货件编号与货物编号匹配的每一行的已销售数量从自己的行。这是什么公式？ （许多公式在Excel中是相同的，所以我也标记Excel。）

解决方案

在产品中尝试这个公式

= C2-ArrayFormula（SUM（IF（Sales！A $ 2：A $ 100 = A2，IF（Sales！B $ 2：B $ 100 = B2，销售！C $ 2：C $ 100））））

假设销售表中的数据高达第100行，根据需要调整

I have two spreadsheets. The first is a list of products, the second is a list of sales.

The products spreadsheet includes four columns: "SKU", "Shipment ID", "Quantity Purchased", and "Remaining Quantity."

     A           B                   C                        D
     SKU      Shipment ID   Qty Purchased   Remaining Qty
1   20121   X2992            40                      38
2   20121   X8483            40                      40
3   71662   X2242            40                      39

The sales spreadsheet includes three columns: "SKU", "Shipment ID", and "Quantity Sold."

     A           B                   C
     SKU      Shipment ID   Qty Sold
1   20121   X2992            1
2   71662   X2242            1
3   20121   X2992            1

I want "Remaining Quantity" to be calculated. Each row of this column should take the "Quantity Purchased" from it's own row and subtract the "Quantity Sold" from the sales spreadsheet for every row where the "SKU" and "Shipment ID" match the "SKU" and "Shipment ID" from its own row. What is the formula for this? (Many formulas are the same in Excel, so I'm tagging Excel as well.)

解决方案

Try this formula in the products sheet cell D2 copied down

=C2-ArrayFormula(SUM(IF(Sales!A$2:A$100=A2,IF(Sales!B$2:B$100=B2,Sales!C$2:C$100))))

That assumes data up to row 100 in Sales sheet, adjust as required

这篇关于Google电子表格：满足两个条件的每一行的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！