Problem using countifs with a table column

[Nociti]

I'm working on a counifs formula that seeks to look at values within a date range and then also check if a table column meets a criteria. Here is the first part of the formula for date range:

=COUNTIFS(Daily!A:A, ">="&Weekly!A3, Daily!A:A, "<="&Weekly!B3) - where Weekly!A3 and Weekly!B3 are specific date values. So far so good. The formula spits out the number of values within the date range.

The problem occurs when I try to add a third IF statement to check the number of times "y" shows up in a table column. If I check ONLY for this condition with the formula: =COUNTIFS(TableName[ColumnName], "y") - it works perfectly.

HOWEVER, when I combine the 2 into: =COUNTIFS(Daily!A:A, ">="&Weekly!A3, Daily!A:A, "<="&Weekly!B3, TableName[ColumnName], "y") - the formula does not work and I get the error #VALUE! Any ideas how to fix this and the logic behind it? Thanks in advance.

 

[maabadi]

In Countif Formula Ranges should have same size.
For this situation, you should SUMPRODUCT.

 

[maabadi]

Try This:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2						ColumnName	Values
3	2	5		2		S	12
4				3		T	13			4
5				4		U	14
6				5		V	15
7				6		W	16
8				2		X	17
9				3		Y	18
10				4		Z	19
11				5		Y	20
12				6		T	21
13				2		Y	22
14				3		S	23
15				4		Y	24
16
Sheet1

Cell Formulas
Range	Formula
J4	J4	=SUMPRODUCT(IFNA(($D$3:$D$20>=$A$3)*($D$3:$D$20<=$B$3)*(Table1[ColumnName]="y"),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Nociti]

Humm, that didn't work. But I appreciate the insight that all conditions on countifs need to be in the same range. I didn't know that.