COUNTIF Function

[Guest]

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

 

[Bernard Liengme]

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

You cannot use full columns (B:B) with SUMPRODUCT
best wishes

 

[T. Valko]

Try this:

=SUMPRODUCT(--(OPEN!B1:B100>=D2),--(OPEN!B1:B100<=C2),--(OPEN!J1:J100>0))

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

 

[Bernard Liengme]

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

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


For more on SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html