# Problem using countifs with a table column

#### Nociti

##### New Member
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.

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

##### Well-known Member
In Countif Formula Ranges should have same size.
For this situation, you should SUMPRODUCT.

##### Well-known Member
Try This:
Book1
ABCDEFGHIJK
1
2ColumnNameValues
3252S12
43T134
54U14
65V15
76W16
82X17
93Y18
104Z19
115Y20
126T21
132Y22
143S23
154Y24
16
Sheet1
Cell Formulas
RangeFormula
J4J4=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

##### New Member
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.

Replies
0
Views
77
Replies
1
Views
90
Replies
1
Views
78
Replies
6
Views
121
Replies
2
Views
99

1,128,103
Messages
5,628,707
Members
416,333
Latest member
Time2Learn

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back