COUNT / INDEX Aging Number Column Data

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
599
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
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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,886
Office Version
  1. 365
Platform
  1. Windows
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))
 

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
599
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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
Joined
Sep 3, 2002
Messages
10,473
Cannot use G:G -- must have signed columns less than an entire column as in G1:G500, etc.
 

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
599

ADVERTISEMENT

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 -
 

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
599
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
Joined
Sep 30, 2003
Messages
493
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,102
Messages
5,768,104
Members
425,455
Latest member
tim851858

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top