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

 

[just_jon]

G1:G65535 ... is a very expensive way. I'll leave it to Aladin to get you to a dynamic range, if you still want SUMPRODUCT...

 

[Aladin Akyurek]

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?

Two options:

In L2 enter & copy down:

1]

=SUMPRODUCT(--($G$2:INDEX(G:G,MATCH(BigNum,G:G)) >= J2),--($G$2:INDEX(G:G,MATCH(BigNum,G:G)) < J3))

I thrust you know what BigNum is.

2]

=SUMPRODUCT(--(USED(G:G) >= J2),--(USED(G:G) < J3))


USED is a function, written in VBA, that you must add as a module to your workbook:

Function Used(r As Range) As Range
'
' Harlan Grove
' Sun 25 Nov 01
'
Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(r.Parent.Cells(1, 1), q))
End Function

Function Used(r As Range) As Range
'
' Harlan Grove
'
Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(Cells(1, 1), q))
End Function

 

[ChrisOK]

Sounded like a good plan - but it didnt work either --

I named my range "Days Open"
Here's the function for the range:
=MASTER_Log!$G:$G

Here's the function on Sheet 2 ( that pulls the aging count over )
=SUMPRODUCT((Days_Open>=15)*(Days_Open<=30))

=SUMPRODUCT((Days_Open,">=15")*(Days_Open,"<=30"))

Last, I tried this:
=SUMPRODUCT(--(Days_Open >= J14),--(Days_Open < J15))

and typed on sheet 2 the 3 references as Aladin did:
J13 holds a zero
J14 holds a 15
J15 holds a 30

Still getting the #NUM!

 

[Texas Longhorn]

Try this:

Insert, Name, Define
Name = BigNum
Refers to = 9.99999999999999E+307
Add

Then change Days_Open to:

Refers to:
=OFFSET($G$1,0,0,MATCH(BigNum,Sheet1!$G:$G),1)

Any luck?

Credit the method to Aladin...I'm just stealing.

 

[Aladin Akyurek]

Sounded like a good plan - but it didnt work either --

I named my range "Days Open"
Here's the function for the range:
=MASTER_Log!$G:$G

No good. You're introducing whole column reference via the backdoor!

...

and typed on sheet 2 the 3 references as Aladin did:
J13 holds a zero
J14 holds a 15
J15 holds a 31

Still getting the #NUM!
...

In J16 enter: 9.99999999999999E+307

In K16 enter & copy down:

=SUMPRODUCT(--(MASTER_Log!$G$2:INDEX(MASTER_Log!G:G,MATCH(BigNum,MASTER_Log!G:G)) >= J13),--(MASTER_Log!$G$2:INDEX(MASTER_Log!G:G,MATCH(BigNum,MASTER_Log!G:G)) < J14))

 

[ChrisOK]

I'd already created the BIGNUM and had used it elsewhere - so it was ready to roll with your idea!

Here's what I pasted into the Days_Open -- and it' works beautifully!

=OFFSET(MASTER_Log!$G$2,0,0,MATCH(BigNum,MASTER_Log!$G:$G),1)

THANKS SO MUCH!!!

 

[Texas Longhorn]

Aladin -

I notice that you sometimes define BigNum in a cell and sometimes in the refers to box. Is there a rule to follow here, or is one more/less expensive than the other?

Thanks,

Bill

 

[Aladin Akyurek]

I'd already created the BIGNUM and had used it elsewhere - so it was ready to roll with your idea!

Here's what I pasted into the Days_Open -- and it' works beautifully!

=OFFSET(MASTER_Log!$G$2,0,0,MATCH(BigNum,MASTER_Log!$G:$G),1)

THANKS SO MUCH!!!

I'd currently prefer:

=MASTER_Log!$G$2:INDEX(MASTER_Log!$G:$G,MATCH(BigNum,MASTER_Log!$G:$G))

I actually used this directly in the formula, avoiding to add another definition to your workbook. Too many dynamic names slows down a spreadsheet.

 

[Aladin Akyurek]

...
I notice that you sometimes define BigNum in a cell and sometimes in the refers to box. Is there a rule to follow here, or is one more/less expensive than the other?...

Defining it via Insert|Name|Define makes life a lot easier. It's true that I sometimes directly use the big number itself in a local computation. One rule that one would observe though is not to create too many dynamic names for performance reasons. I'd make an exception for Bignum and BigStr (the former a constant, the latter a formula).