根据两个表之间的公共ID对苹果进行计数 [英] Count apple based on common ID between two tables

问题描述

我有两个看起来像这样的Excel表:

I have two Excel tables that look like this:

表1

 A          B         C 
ID    Stocked      Sale
1       shelf      sold
5     display      sold
9       shelf      sold
12      shelf      sold
13      shelf

表2

 P          Q 
ID    Stocked
a2      apple
88
83
1       apple
9       apple

鉴于苹果是:

• 表1的货架上有货
• 在表1中出售

因此，结果必须是2个苹果.

So, the outcome has to be 2 apples.

这是我到目前为止所尝试的:

Here is what I have tried thus far:

COUNTIFS(B:B, "shelf", C:C, "sold", Q:Q, "apple")

我不知道如何通过通用ID进行计数...

I can't figure out how to count by common id...

推荐答案

类似的方法应该对您有用:

Something like this should work for you:

=SUMPRODUCT(COUNTIFS(A:A,REPT($P$2:$P$6,$Q$2:$Q$6="apple"),B:B,"shelf",C:C,"sold"))

这篇关于根据两个表之间的公共ID对苹果进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！