If over 60, then show all detail

[Carla]

I need to know the whole picture for problem customers, not just individual
invoices that are past due.

I have a table that shows details for any invoice with a balance due. It
also specifies inv amt in the appropriate column (current, over 30, over 60,
over 90). I need a formula that will say if any customer has any invoices in
over 60 or over 90 to show ALL detail for customer, not just specific line
items for that customer that are actually over 60 or over 90.

Thank you for your expertise!
Carla

 

[Bernie Deitrick]

Carla,

You could use a helper column of formulas, along the lines of

=IF(SUMPRODUCT(($A$2:$A$1000=A2)*($C$2:$C$1000="Over 60"))>0,"Show me","")

where column A has some unique customer ID and column C indicates the customer has at least one item
that is over 60 - I have assumed that the "Over 60" indicator is a string - if it is a non-zero
amount, then you could use

=IF(SUMPRODUCT(($A$2:$A$1000=A2)*($C$2:$C$1000>0))>0,"Show me","")

Then copy down to match your data, and filter on that column to show "Show me" strings only.

HTH,
Bernie
MS Excel MVP