Sumproduct on a table

[niheav]

Hi all, apologies if there is an answer to something along these lines already, but I have extensively searched and I can't find anything to apply to this issue.

I have a formula that works the way I want it to, however I want to apply similar formulas to 2 tables which need independent filters (therefore formatted as a table) - when I convert to a table the formula no longer works.

What I am trying to do is keep track of attendance, so dynamic dates and I want it filtered by the year of birth of the participant and counting the "Yes" in the appropriate column:

This is the screenshot oof what I have
The formulas in J2 worked pre formatting as table, and that formula is:

=SUMPRODUCT(($C$1:$F$16="Yes")*($C$1:$F$1=$K$2)*($A$1:$A$16=H2))

However if I try change this to table format of:
=SUMPRODUCT((Table2[[#All],[08/10/2022]:[17/09/2022]]="Yes")*(Table2[[#Headers],[08/10/2022]:[17/09/2022]]=$K$2)*(Table2[[#All],[Year Born]]=H2))

I then get a result of 0

Any help much appreciated!

Thanks,

N

 

[Eric W]

Welcome to the MrExcel forum!

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Year Born	Player Name	10/8/2022	10/1/2022	9/24/2022	9/17/2022					For Date:
2	2000	Player 1	Yes	Yes	Yes			2002	Players	2	10/8/2022
3	2000	Player 2	Yes			Yes		2001	Players	1
4	2000	Player 3		Yes	Yes			2000	Players	2
5	2001	Player 4	Yes	Yes	Yes	Yes
6	2002	Player 5	Yes	Yes
7	2002	Player 6	No		Yes	Yes
8	2002	Player 7	Yes
Sheet1

Cell Formulas
Range	Formula
J2:J4	J2	=SUMPRODUCT((Table1[#Data]="Yes")*(Table1[Year Born]=H2)*(Table1[#Headers]=TEXT($K$2,"m/d/yyyy")))

I used [#Headers] instead of trying to use the starting/ending dates as you did. It ends up checking more cells than it has to, but the formula is shorter. Also, apparently when a date becomes a header in a table, it becomes text data. That's why I needed to use the TEXT function in the formula.

Hope this helps!

 

[niheav]

Perfect!
That works a treat!

It was the text formatting that was catching me out!!

Appreciate your help and the quick turnaround!