I wrote a formula below to count unique rows using nine non-adjacent criteria ranges from the source data. The unique rows are based on the values in column-G in the source data, that is, if there are duplicate items in the columnG the formula just looks at one. Criteria ranges PmtTypeRng and DebtAgeRng have a set of multiple items/criteria.
The formula sadly return null. Not sure what i'm doing wrong, will appreciate any insight into this or alternative way to write it.
=SUM(IF(FREQUENCY(IF((DateMonthRng=$C$4)(ZoneRng=$E$4)(ClientRng=$C2)(ClientAccRng=$D2)(DistroRng=$E2)(PaymtDelbyRng="Customer")(PmtTypeRng={"DirectDebit","Cash","CreditCard"})(DebtAgeRng={"5-day","10-day","20-day"}))(PaymtEffect_Rng="Debit"),(MATCH(G2,G:G,0)),MATCH(G2,G:G,0))>0,1,0))
Thanks
Bookmarks