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.

 

[Fluff]

Check that your dates are real dates & not text, as it works for me.

+Fluff 1.xlsm
	G	H	I	J	K	L
4	Column1	Date			Column1	Column2
5	a	22/03/2022			b	y
6	b	26/03/2022			d	y
7	c	27/03/2022			e
8	d	28/03/2022			a	y
9	e	29/03/2022			f	y
10					g	y
11					p
12	2
Main

Cell Formulas
Range	Formula
G12	G12	=SUMPRODUCT((COUNTIFS(Table2[Column1],Table1[Column1],Table2[Column2],"y"))*(Table1[Date]>=TODAY()))

 

[BlueRuby001]

it

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

View attachment 60940

 

[BlueRuby001]

Check that your dates are real dates & not text, as it works for me.

+Fluff 1.xlsm
	G	H	I	J	K	L
4	Column1	Date			Column1	Column2
5	a	22/03/2022			b	y
6	b	26/03/2022			d	y
7	c	27/03/2022			e
8	d	28/03/2022			a	y
9	e	29/03/2022			f	y
10					g	y
11					p
12	2
Main

Cell Formulas
Range	Formula
G12	G12	=SUMPRODUCT((COUNTIFS(Table2[Column1],Table1[Column1],Table2[Column2],"y"))*(Table1[Date]>=TODAY()))

My dates are formatted as dates. I think it has to do with my tables being on different tabs and not on the same sheet as in your screenshot. When i moved my dummy tables to the same sheet the formula worked, but when i try it with them on separate tabs it doesn't work:

 

[Fluff]

My dates are formatted as dates.

That does not mean anything, they could still be text. If you change the cell format of those dates to General what do you see?

 

[BlueRuby001]

That does not mean anything, they could still be text. If you change the cell format of those dates to General what do you see?

When i change it to General the result is still 0. I ended up just creating a new workbook though and the formula works fine in the new file. I think the other one may just have been corrupt/glitched, i've had issues with the excel software before with functions not working and i have to close and reopen the file then they work. Thank you again for all your help I really appreciate it so much!

 

[Fluff]

When i change it to General the result is still 0

Yes but did the dates still look like dates?

 

[BlueRuby001]

Yes but did the dates still look like dates?

They looked like this when switching dates to general format:

 

[Fluff]

In that case they are dates, so don't know why it didn't work initially.
But glad you got it sorted.