Problem using countifs with a table column

Nociti

New Member
Joined
Nov 23, 2019
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,847
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
In Countif Formula Ranges should have same size.
For this situation, you should SUMPRODUCT.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,847
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Nov 23, 2019
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,407
Messages
5,641,968
Members
417,249
Latest member
serrulate

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top