Excel 2007 : Replacing an Array Formula

[stevetothink]

I have some array formulas that are hogging the resources in my excel file. They are performing a unique count for me. Is there anyway to rewrite this formula so it is not an array?

{=SUM(IF(FREQUENCY(IF('MASTER LEDGER'!DF12:DF5001=P14,IF('MASTER LEDGER'!C12:C5001<>"",MATCH('MASTER LEDGER'!C12:C5001,'MASTER LEDGER'!C12:C5001,0))),ROW('MASTER LEDGER'!C12:C5001)-ROW('MASTER LEDGER'!C12)+1),1))}

Thanks,
Steve

[NBVC]

You would need to add a helper column that identifies the unique rows and then simply count those identifiers...

A countifs might work...

e.g.

=--(COUNTIFS(DF$12:DF12,'Sheet1'!$P$14,C$12:C12,C12)=1)

copied down.

This formula would go in a new column of the Master Ledger sheet, and Sheet1 is the sheet P14 is located on.

copy formula down

Then you can simply sum that column for a final count.