How many cells in range1 are contaned and not contained in Range2 (No help column)

[drom]

Hi and thanks in advance!

I have 2 diferent sets of data

• Sheet(1).Range("A2:A50") named "Range1"
• Sheet(2).Range("A20:C350") named "Range2"

My questions:

1. I would like to have in Sheet(2).Range("A1") a formula probably SumProduct to sum how many of my cells in Range2 are not contained in Range1
2. I would like to have in Sheet(2).Range("A10") a formula probably SumProduct to sum how many of my cells in Range2 are contained in Range1

ps:
I do not want to use a help Column (Why: because using a help column I know the how to accomplish
I am using conditional formatting to highlight those cells, but how can I count/Sum them?

Thanks again

 

[Fluff]

For 2 use
=SUM(COUNTIF(Range2,Range1))
For 1
=Counta(Range2)-A10