COUNTIF Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

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....
 
Try:
=SUMPRODUCT(--(OPEN!$B1:$B1000>=D2),--(OPEN!$B1:$B1000<=C2),
OPEN!$J1:$J1000)

You cannot use full columns (B:B) with SUMPRODUCT
best wishes
 
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.
 
Back
Top