# COUNT / INDEX Aging Number Column Data

#### ChrisOK

##### Well-known Member
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.....

PS - Column G is ever-growing in rows...
It'd need to look at G:G
Book2
ABCDEFGHI
1LOG#PartNumberAnalystDateReleasedDate Signed-offDateReturnedDaysOpenActualCycleTime
21000XXJones10/20/200310/20/200310/22/20031202>14days
31000YYHawes10/21/200310/21/200310/22/20031191>14days
41000ZZHawes10/22/200310/25/200310/22/20031180>14days
51001SSSmith11/15/200311/30/200311/30/20039415>14days
61001WWJones1/1/20041/15/2004470>14days
71002DDHawes1/31/20042/5/200417015-30days
81002FFHawes2/1/200416015-30days
91003GGSmith2/7/2004100<14days
101003UUJones2/13/200440<14days
111004LLLHawes2/14/200430<14days
121004JJJHawes2/15/200420<14days
131005AAASmith2/17/200400<14days
Sheet1

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### 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))

##### MrExcel MVP
Book6.xls
GHIJKL
1DaysOpenActualCycleTime
2120200-145
311911515-302
4118031>305
594151.00E+308
6470
7170
8160
9100
1040
1130
1220
1300
14
Sheet1

L2, copied down to L5...

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

#### ChrisOK

##### Well-known Member
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...

##### MrExcel MVP
ChrisOK said:
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

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

#### ChrisOK

##### Well-known Member
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 -

##### MrExcel MVP
ChrisOK said:
I tried using COUNTIF as well -- and couldnt get it to work --

Try SumProduct once more.

#### ChrisOK

##### Well-known Member
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

##### Active Member
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.

Replies
7
Views
126
Replies
11
Views
189
Replies
1
Views
119
Replies
1
Views
99
Replies
9
Views
88

1,186,927
Messages
5,960,599
Members
438,486
Latest member
ncc84330

### 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.

### Which adblocker are you using?

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

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