COUNTIF Function
 

I am working on a collection of data and I would like to create an aging
summary. In column B I have transaction dates and in column J I have account
balances.

At the top of the summary is a table that contains my date ranges:

0-30 8/25/2007-7/26/2007
31-60 7/25/2007-6/26/2007
61-90 6/25/2007-5/27/2007
91-120 5/26/2007-4/27/2007
121-150 4/26/2007-3/28/2007
151-365 3/27/2007-8/25/2006
366+ 8/24/2006-1/1/2006


I want to be able to count cells in column B that fall within a specified
date range AND have an account balance (column J) greater than 0.

I can get the cells within a date range, but I can't seem to get the last
half of the puzzle. Here is what I have so far:

=COUNTIF(OPEN!$B:$B,"="& D2)-COUNTIF(OPEN!B:B,""& C2)

What do I need to add on to this to complete my formula...or am I starting
with the wrong base to begin with....

COUNTIF Function
 

Try:
=SUMPRODUCT(--(OPEN!$B1:$B1000=D2),--(OPEN!$B1:$B1000<=C2),
OPEN!$J1:$J1000)

You cannot use full columns (B:B) with SUMPRODUCT
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Natalie50208" wrote in message
...
I am working on a collection of data and I would like to create an aging
summary. In column B I have transaction dates and in column J I have
account
balances.

At the top of the summary is a table that contains my date ranges:

0-30 8/25/2007-7/26/2007
31-60 7/25/2007-6/26/2007
61-90 6/25/2007-5/27/2007
91-120 5/26/2007-4/27/2007
121-150 4/26/2007-3/28/2007
151-365 3/27/2007-8/25/2006
366+ 8/24/2006-1/1/2006


I want to be able to count cells in column B that fall within a specified
date range AND have an account balance (column J) greater than 0.

I can get the cells within a date range, but I can't seem to get the last
half of the puzzle. Here is what I have so far:

=COUNTIF(OPEN!$B:$B,"="& D2)-COUNTIF(OPEN!B:B,""& C2)

What do I need to add on to this to complete my formula...or am I starting
with the wrong base to begin with....

COUNTIF Function
 

Try this:

=SUMPRODUCT(--(OPEN!B1:B100=D2),--(OPEN!B1:B100<=C2),--(OPEN!J1:J1000))

Note that you can't use entire columns as range references with Sumproduct
unless you're using Excel 2007.


--
Biff
Microsoft Excel MVP


"Natalie50208" wrote in message
...
I am working on a collection of data and I would like to create an aging
summary. In column B I have transaction dates and in column J I have
account
balances.

At the top of the summary is a table that contains my date ranges:

0-30 8/25/2007-7/26/2007
31-60 7/25/2007-6/26/2007
61-90 6/25/2007-5/27/2007
91-120 5/26/2007-4/27/2007
121-150 4/26/2007-3/28/2007
151-365 3/27/2007-8/25/2006
366+ 8/24/2006-1/1/2006


I want to be able to count cells in column B that fall within a specified
date range AND have an account balance (column J) greater than 0.

I can get the cells within a date range, but I can't seem to get the last
half of the puzzle. Here is what I have so far:

=COUNTIF(OPEN!$B:$B,"="& D2)-COUNTIF(OPEN!B:B,""& C2)

What do I need to add on to this to complete my formula...or am I starting
with the wrong base to begin with....

COUNTIF Function
 

Sorry, I forgot to test for 0
To COUNT cells
=SUMPRODUCT(--(OPEN!$B1:$B1000=D2),--(OPEN!$B1:$B1000<=C2), --(OPEN!$J1:$J10000))

To ADD cells
=SUMPRODUCT(--(OPEN!$B1:$B1000=D2),--(OPEN!$B1:$B1000<=C2), --(OPEN!$J1:$J10000,
OPEN!$J1:$J1000))


For more on SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
Try:
=SUMPRODUCT(--(OPEN!$B1:$B1000=D2),--(OPEN!$B1:$B1000<=C2),
OPEN!$J1:$J1000)

You cannot use full columns (B:B) with SUMPRODUCT
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Natalie50208" wrote in message
...
I am working on a collection of data and I would like to create an aging
summary. In column B I have transaction dates and in column J I have
account
balances.

At the top of the summary is a table that contains my date ranges:

0-30 8/25/2007-7/26/2007
31-60 7/25/2007-6/26/2007
61-90 6/25/2007-5/27/2007
91-120 5/26/2007-4/27/2007
121-150 4/26/2007-3/28/2007
151-365 3/27/2007-8/25/2006
366+ 8/24/2006-1/1/2006


I want to be able to count cells in column B that fall within a specified
date range AND have an account balance (column J) greater than 0.

I can get the cells within a date range, but I can't seem to get the last
half of the puzzle. Here is what I have so far:

=COUNTIF(OPEN!$B:$B,"="& D2)-COUNTIF(OPEN!B:B,""& C2)

What do I need to add on to this to complete my formula...or am I
starting
with the wrong base to begin with....