Excel 2007 : Count blank cells in one column that also meet a date condition in another column

Hi,

Please help me as soon as possible. I need to count the blank cells in one column (the Date received column) that also meet a date condition in another column (the Transfer Date column). I also need to add up all the account balances (in the Account Balances column) for the accounts that were just counted.

I attached an illustrative workbook. the first tab has the raw information. The two pieces of analytical information sought are on the second tab. First, I need to identify and count the accounts that were received more than 120 days ago and for which there is not tranfer date entered. Then for those identified and counted accounts, I need a summary of the corresponding balances.

I just can't figure this out. I've tried conditional formatting, countif, IF combined with COUNT, and more. I get it working for partial steps, but not enough to complete the task.

I would greatly appreciate some help here, asap. Thanks. God Bless.

Try, respectively:

=SUMPRODUCT(--(Sheet1!$C$2:$C$12=""),--(Sheet1!$A$2:$A$12<TODAY()-120))

and

=SUMPRODUCT(--(Sheet1!$C$2:$C$12=""),--(Sheet1!$A$2:$A$12<TODAY()-120),Sheet1!B2:B12)

Try then:

=SUMPRODUCT(--(Sheet1!$C$2:$C$12=""),--(Sheet1!$A$2:$A$12<>""),--(Sheet1!$A$2:$A$12<TODAY()-120))

and

=SUMPRODUCT(--(Sheet1!$C$2:$C$12=""),--(Sheet1!$A$2:$A$12<>""),--(Sheet1!$A$2:$A$12<TODAY()-120),Sheet1!B2:B12)

There was a good like at Xldynamic.com.. but it appears to no longer be available...

... basically the dashes coerce the resulting array of TRUE/FALSEs for each condition to 1's and 0's so that Sumproduct can do the math as designed...

Go to the Formulas menu and click Evaluate Formula.. then you can keep clicking Evaluate to see how the function works.