COUNTIFS between dates plus multiple criteria

[Rodrigo Pombo]

Hi:

Any help would be greatly appreciated.

When using this works fine =3
=COUNTIFS(Actual_Completion_Date,">=1/1/2015",Actual_Completion_Date,"<=31/1/2015")

But when adding additional criteria
=COUNTIFS(Actual_Completion_Date,">=1/1/2015",Actual_Completion_Date,"<=31/1/2015",Case_Region1,C33)

I get #VALUE!

Thanks

 

[Khalid NGO]

Hi Rodrigo,
Welcome to the forum Chandoo.org...

Can you post your xl file with sample data?
This will help users to help you...

Regards,

 

[NARAYANK991]

Hi Rodrigo ,

One possibility is when the range sizes do not match ; if the range Actual_Completion_Date is 'x' rows / columns in size , and the range Case_Region1 is 'y' rows / columns in size , where 'x' is not equal to 'y' , then the formula can display the #VALUE! error.

Another possibility is if the two named ranges are not single-column or single-row ranges ; if one of them is a single-column / single-row range , and the other is a multiple-column / multiple-row range , then also Excel will display the error value.

Narayan

 

[Rodrigo Pombo]

Hi Khalid:

Thank you so much for the swift reply. I just found the answer to this problem, and let’s call it the power of the Chandoo forums!

@ NARAYAN: you were right and thank you to you as well, it was the size of the ranges (number of rows), see below:

As I was preparing a sample file as requested, whilst deleting some confidential data, I deleted some of the last rows of the table. As a result, the dynamic range containing the dates did finish in a cell with data (in this case a date). So in summary the issue was the dynamic date range was not of the same length as the other ranges used when adding other criteria.

But this raises another question: how to have a dynamic range that is of the same length as the last row of the complete database even if there are empty cell at the end? This, by not touching the data source (i.e. adding manually data to replace empty cells).

Many thanks again,

Rodrigo

P.D. I'm not able to attached a file due to internal controls.

Hi Rodrigo ,

One possibility is when the range sizes do not match ; if the range Actual_Completion_Date is 'x' rows / columns in size , and the range Case_Region1 is 'y' rows / columns in size , where 'x' is not equal to 'y' , then the formula can display the #VALUE! error.

Another possibility is if the two named ranges are not single-column or single-row ranges ; if one of them is a single-column / single-row range , and the other is a multiple-column / multiple-row range , then also Excel will display the error value.

Narayan

 

[NARAYANK991]

Hi Rodrigo ,

The simplest solution would be to use tables !

However , if you are using named ranges , the correct way would be to use one column to establish the length of the database ; suppose we use something like this , named ColA :

=$A$3:INDEX($A:$A,COUNTA($A:$A))

to establish the length of the database , assuming that column A will not have any empty cells within the database.

Now , all other named ranges within the database would be defined using ColA as the basis , as in ColB would be defined as :

=Sheet1!$B$3:INDEX(Sheet1!$B:$B,ROWS(ColA)+ROW(ColA)-1)

When ever the range ColA adjusts itself to added rows , all other named ranges within the database will be adjusted automatically.

Narayan

 

[Rodrigo Pombo]

Hi Rodrigo ,

The simplest solution would be to use tables !

However , if you are using named ranges , the correct way would be to use one column to establish the length of the database ; suppose we use something like this , named ColA :

=$A$3:INDEX($A:$A,COUNTA($A:$A))

to establish the length of the database , assuming that column A will not have any empty cells within the database.

Now , all other named ranges within the database would be defined using ColA as the basis , as in ColB would be defined as :

=Sheet1!$B$3:INDEX(Sheet1!$B:$B,ROWS(ColA)+ROW(ColA)-1)

When ever the range ColA adjusts itself to added rows , all other named ranges within the database will be adjusted automatically.

Narayan

 

[Rodrigo Pombo]

Hi Narayan:

That is a good advice and a simple solution. It will work really well for this database type.

Thanks a lot for your help.

Kind regards,
Rodrigo