满足两个条件的每一行的总和 [英] Sum of every row where two conditions are met
问题描述
我有两个电子表格.第一个是产品列表，第二个是销售列表.
产品电子表格包含四列:"SKU"，货件ID"，已购买数量"和剩余数量".
n&nspsp; ; C b ; D
购买的数量
1 X2992 38
2 X8483 nbsp;&nspsp; bsp 40
3 X2242 nbsp;&nspsp; &nspsp; 39
销售电子表格包含三列:"SKU"，货件ID"和售出数量".
n&nspsp; ; C
SKU
1 X2992
2 X2242
3 X2992 &bb>
我希望计算剩余数量".该列的每一行都应从其自己的行中获取购买的数量"，并从销售电子表格中减去"SKU"和"Shipment ID"与"SKU"和"Shipment ID"匹配的每一行的已售数量"从自己的行.公式是什么? (许多公式在Excel中是相同的，因此我也对Excel进行了标记.)
在向下复制的产品表单元格D2中尝试使用此公式
=C2-ArrayFormula(SUM(IF(Sales!A$2:A$100=A2,IF(Sales!B$2:B$100=B2,Sales!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
这篇关于满足两个条件的每一行的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
