COUNT / INDEX Aging Number Column Data

[ChrisOK]

I'm trying to create a quick AGING table....
On Sheet 1 the log of items exists..
On Sheet 2 - the Aging Info exists....

I'd like to have Sheet 2 look at Sheet 1 and count up how many items in column G are beyond the 14 day range.... > than 14

I need another cell to count up how many in Col G fall between 15 and 30 days.... =>15 and =<30

I need another cell to count up how many in Col G are =14 days old or BELOW =<14

I figured plugging in a conditional formating function might be the best way to do it -- but then thought that just entering an INDEX / LOOKUP / COUNTIF function on Sheet 2 might work better.....

Thanks in advance for your help to determine the best way...
PS - Column G is ever-growing in rows...
It'd need to look at G:G

Book2
	A	B	C	D	E	F	G	H	I
1	LOG#	PartNumber	Analyst	DateReleased	Date Signed-off	DateReturned	DaysOpen	ActualCycleTime
2	1000	XX	Jones	10/20/2003	10/20/2003	10/22/2003	120	2	>14days
3	1000	YY	Hawes	10/21/2003	10/21/2003	10/22/2003	119	1	>14days
4	1000	ZZ	Hawes	10/22/2003	10/25/2003	10/22/2003	118	0	>14days
5	1001	SS	Smith	11/15/2003	11/30/2003	11/30/2003	94	15	>14days
6	1001	WW	Jones	1/1/2004	1/15/2004		47	0	>14days
7	1002	DD	Hawes	1/31/2004	2/5/2004		17	0	15-30days
8	1002	FF	Hawes	2/1/2004			16	0	15-30days
9	1003	GG	Smith	2/7/2004			10	0	<14days
10	1003	UU	Jones	2/13/2004			4	0	<14days
11	1004	LLL	Hawes	2/14/2004			3	0	<14days
12	1004	JJJ	Hawes	2/15/2004			2	0	<14days
13	1005	AAA	Smith	2/17/2004			0	0	<14days
Sheet1

 

[Joe4]

You should be able to do this with a series of COUNTIF functions (if only one criteria) and SUMPRODUCT functions (if more than one criteria). Here are a few to get you starrted:

I'd like to have Sheet 2 look at Sheet 1 and count up how many items in column G are beyond the 14 day range.... > than 14

=COUNTIF(G2:G13,">14")

I need another cell to count up how many in Col G fall between 15 and 30 days.... =>15 and =<30

=SUMPRODUCT((G2:G13>=15)*(G2:G13<=30))

 

[Aladin Akyurek]

Book6.xls
	G	H	I	J	K	L
1	DaysOpen	ActualCycleTime
2	120	2		0	0-14	5
3	119	1		15	15-30	2
4	118	0		31	>30	5
5	94	15		1.00E+308
6	47	0
7	17	0
8	16	0
9	10	0
10	4	0
11	3	0
12	2	0
13	0	0
14
Sheet1

L2, copied down to L5...

=SUMPRODUCT(--($G$2:$G$13 >= J2),--($G$2:$G$13< J3))

 

[ChrisOK]

The greater than and less than work great -- but the SUMPRODUCT one is not working....
I'm getting a #NUM! error

Wanted to clarify --
I don't want to add each of the items together --
I just want to count how many cells hold a result of 15:30 days
= to 15 and greater
= to 30 and less

Here's the function I'm using:
=SUMPRODUCT((MASTER_Log!G:G>=15)*(MASTER_Log!G:G<=30))

Master_Log! is the Sheet 1 that holds the list that I posted as an example.

I've even gone back in and tried adding the comma and quotes such as the other...

=SUMPRODUCT((MASTER_Log!G:G,">=15")*(MASTER_Log!G:G,"<=30"))

this does not work either...

 

[Aladin Akyurek]

The greater than and less than work great -- but the SUMPRODUCT one is not working....
I'm getting a #NUM! error

Wanted to clarify --
I don't want to add each of the items together --
I just want to count how many cells hold a result of 15:30 days
= to 15 and greater
= to 30 and less

Here's the function I'm using:
=SUMPRODUCT((MASTER_Log!G:G>=15)*(MASTER_Log!G:G<=30))

Master_Log! is the Sheet 1 that holds the list that I posted as an example.

It is not allowed to use whole columns like G:G with SumProduct.

 

[just_jon]

Cannot use G:G -- must have signed columns less than an entire column as in G1:G500, etc.

 

[ChrisOK]

I tried using COUNTIF and COUNT as well -- and couldnt get it to work --

=COUNTIF((MASTER_Log!G:G,">=15")*(MASTER_Log!G:G,"<=30"))
AND
=COUNTIF((MASTER_Log!G:G>=15)*(MASTER_Log!G:G<=30))

neither work -

 

[Aladin Akyurek]

I tried using COUNTIF as well -- and couldnt get it to work --

Try SumProduct once more.

 

[ChrisOK]

but you said:

"It is not allowed to use whole columns like G:G with SumProduct."

and I need the function to look at everything in Col G of Sheet 1 as they add new rows of data to count....

If I put a limitation of G2:G:500 then it will stop calculating properly at 500 ( as they'll have no idea how to re-build it to pick up 501 down.... )

If SUMPRODUCT can't utilize g:g -- what can?

 

[Texas Longhorn]

As an alternative to referencing the entire column, you might consider a dynamic named range for your data in column G. Then use the SUMPRODUCT Aladin recommended, but reference the range name instead.

If you need more on this, just let me know.