Countifs with match and multiple criteria between two tables

[BlueRuby001]

Wracking brain for two days on this.
Have two tables and trying to count how many in table 1 column A match table 2 column A and of those in table 1 that have matches, how many of those have the value "Y" in table 2 column B.

Tried variations of countifs with match, sumproduct etc with no luck so far and im sure i missed something stupidly simple.

Help is greatly appreciated.

 

[bebo021999]

Could you attach a screenshot/image/XL2BB?
(How to use XL2BB tool: XL2BB - Excel Range to BBCode)

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUMPRODUCT((ISNUMBER(MATCH(Table2[Column1],Table1[Column1],0)))*(Table2[Column2]="Y"))

 

[BlueRuby001]

Hi & welcome to MrExcel.
How about

=SUMPRODUCT((ISNUMBER(MATCH(Table2[Column1],Table1[Column1],0)))*(Table2[Column2]="Y"))

OMG! That works!! Thank you thank you thank you!!! I think i messed my variations previously up by incorporating the second criteria in the same brackets as the ISNUMBER function.
One last question, how would I incorporate a third criteria such as "of those that are matches and "Y" are also greater than or equal to today()? i tried adding a *(third criteria) onto the end but it didn't take:

again i cannot thank you enough for your help, I normally figure these out on my own, but this is the first one to stump me.

 

[Fluff]

You need to remove the quotes from the >=

 

[BlueRuby001]

You need to remove the quotes from the >=

i tried that as well but get #NA:

 

[Fluff]

Are both tables the same size?

 

[BlueRuby001]

Are both tables the same size?

no they're different sizes (different number of columns and rows)

 

[Fluff]

Ok, how about

=SUMPRODUCT((COUNTIFS(Table2[Column1],Table1[Column1],Table2[Column2],"y"))*(Table1[Date]>=TODAY()))

 

[BlueRuby001]

it

Ok, how about

=SUMPRODUCT((COUNTIFS(Table2[Column1],Table1[Column1],Table2[Column2],"y"))*(Table1[Date]>=TODAY()))

it returns the wrong count value (same count as without the date criteria):