Count New to the List, Count Leaving the List, Count Total

[SteveC]

Ok, this is a variation of a previous question

Column D is a list of data at the beginning of the month. Column C is the
list of data at the end of the month.

What formulas do I need in cells B1, B2, B3 and B4 to get the answers I
provided in Column B below? No helper columns. Thanks very much for taking
a look.


Column A Column B Column C Column D
Beginning of month 5 apples apples
New to the list 5 pears grapefruits
Leaving the list 3 bananas beans
End of month 7 green green
blue red
yellow
brown

 

[SteveC]

I think this works:

cell B1 =COUNTIF($D$1:$D$2000,">*")
Cell B2
=COUNTIF($C$1:$C$2000,">*")-SUMPRODUCT(--(ISNUMBER(MATCH(C1:C2000,D12000,0))))
Cell B3
=-(COUNTIF($D$1:$D$2000,">*")-SUMPRODUCT(--(ISNUMBER(MATCH(C1:C2000,D12000,0)))))
Cell B4
=-(COUNTIF($D$1:$D$2000,">*")-SUMPRODUCT(--(ISNUMBER(MATCH(C1:C2000,D12000,0)))))