Count Dates in a range based on Column Header Id without using sum-product

[sam999]

Hello All,
I want to use the countif function to count dates in a range based o
Column HEADER at the Top. For example I want to count all X005 betwee
01st and 10th of May, 2012.
I am looking for a dynamic countif function using look up formula whic
could be replicated for other IDs like X006, X007 without highlightin
the column number.
The problem is 2-fold:
1. Select column X005
2. Count dates between 01st and 10th of May

So, Once I copy over this formula in the cell below it would populate
the count for X006

I do not want to use sum-product as it takes up lot of memory an
freezes excel if i try to change a date.

The Data is shown Below: ( Actual Data runs 1000s of lines)

ID X005 X006 X007 X008 X009 X010 X011
A001 25-May-12 2-Jul-12 28-Sep-12 18-Jun-12 6-Jul-12 9-Sep-12 25-Aug-12
A002 25-May-12 2-Jul-12 28-Sep-12 18-Jun-12 6-Jul-12 9-Sep-12 25-Aug-12
A003 25-May-12 2-Jul-12 28-Sep-12 8-Jun-12 6-Jul-12 17-Sep-12 25-Aug-12
A004 25-May-12 2-Jul-12 28-Sep-12 29-Jun-12 6-Jul-12 17-Sep-12 25-Aug-12
A005 25-May-12 9-Jul-12 4-Oct-12 8-Jun-12 13-Jul-12 16-Sep-12 31-Aug-12
A006 25-May-12 9-Jul-12 4-Oct-12 11-Jun-12 13-Jul-12 16-Sep-12 31-Aug-12
A007 02-May-12 9-Jul-12 4-Oct-12 14-Jun-12 13-Jul-12 16-Sep-12 31-Aug-12

Kindly let me know if this is possible. I appreciate all the help i
this regard.

Thanks
Sa

 

[zvkmpw]

I want to use the countif function to count dates in a range based on

Column HEADER at the Top. For example I want to count all X005 between
01st and 10th of May, 2012.
I am looking for a dynamic countif function using look up formula which
could be replicated for other IDs like X006, X007 without highlighting
the column number.
The problem is 2-fold:
1. Select column X005
2. Count dates between 01st and 10th of May

So, Once I copy over this formula in the cell below it would populates
the count for X006

I do not want to use sum-product as it takes up lot of memory and
freezes excel if i try to change a date.

The Data is shown Below: ( Actual Data runs 1000s of lines)

ID X005 X006 X007 X008 X009 X010 X011
A001 25-May-12 2-Jul-12 28-Sep-12 18-Jun-12 6-Jul-12 9-Sep-12 25-Aug-12
A002 25-May-12 2-Jul-12 28-Sep-12 18-Jun-12 6-Jul-12 9-Sep-12 25-Aug-12
A003 25-May-12 2-Jul-12 28-Sep-12 8-Jun-12 6-Jul-12 17-Sep-12 25-Aug-12
A004 25-May-12 2-Jul-12 28-Sep-12 29-Jun-12 6-Jul-12 17-Sep-12 25-Aug-12
A005 25-May-12 9-Jul-12 4-Oct-12 8-Jun-12 13-Jul-12 16-Sep-12 31-Aug-12
A006 25-May-12 9-Jul-12 4-Oct-12 11-Jun-12 13-Jul-12 16-Sep-12 31-Aug-12
A007 02-May-12 9-Jul-12 4-Oct-12 14-Jun-12 13-Jul-12 16-Sep-12 31-Aug-12

I'm not following exactly how you want somebody to interact with the spreadsheet, or what problems you've had so far.

However, the following does seem to get the desired result.

I put your data in columns A:H, and added a few thousand more rows to test for performance problems.

I used K1 and K2 to hold (respectively) the user-chosen earliest date and latest date to be included in the count.

I used K3 to hold the user-chosen column-header value; for example, X007.

For convenience, I put this formula in K4:
=MATCH(K3,B1:J1)
It calculates the column number from the column-header value.

Then the result is:
=COUNTIF(OFFSET(A1,1,K4,10000,1),">="&K1)
-COUNTIF(OFFSET(A1,1,K4,10000,1),">"&K2)
If there can be more than 10000 rows, increase the two appearances of "10000".

The calculation completes with no discernible delay. I'm using Excel 2003 on Windows XP with a ten-year-old Dell desktop PC.

Hope I got the requirements right!

Modify as needed.