如果列C的值为X，则Countif with Subtraction

Question

我有一张表格，上面列出了可供选择的产品。

列b为名称，列"D“为空，除非装运，然后在该框中放入"X”

然后在"F“列中，我使用unique获得B列的值。

=sort(UNIQUE(B4:B100),1,True)

我想要的是如果"X“在"D”列，那么从"G“列的总数中减去。

=COUNTIF($B$4:B100,G4)

https://docs.google.com/spreadsheets/d/1BeuvT3ACYYpWWhrg7UKdQe2erY_P-czA73tV6qEBzdY/edit?usp=sharing

Answer 1

=ARRAYFORMULA({INDEX(QUERY(B4:B, 
 "select B,count(B) where B is not null group by B label count(B)''", 0),,1), 
 QUERY(IFERROR(QUERY(B4:B, 
 "select count(B) where B is not null group by B label count(B)''", 0)-
 IFERROR(VLOOKUP(INDEX(QUERY(B4:B, 
 "select B,count(B) where B is not null group by B label count(B)''", 0),,1), QUERY(B4:D, 
 "select B,count(B) where D is not null group by B label count(B)''", 0), 2, 0))), 
 "where Col1 is not null", 0)})

​

​