how to populate unique count in sheet 1 when the data is present in sheet 2.

=SUMPRODUCT(IFERROR((Sheet2!B1:B10="Completed")*1/COUNTIF(Sheet2!A1:A10,Sheet2!A1:A10),0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Hi Try this array

Formula:

Please Login or Register  to view this content.

Hi, Since it's an array formula it will take time, you can set the formula calculation mode to manual during data entry and calculate whever you need but that too will be slow,

But i suggest you to put a helper column which will results

Formula:

Please Login or Register  to view this content.

=1 in sheet2 and use normal sum formula in sheet1 will improve the speed.

Ref the attachment..

Originally Posted by Vishnu Balan

But since my excel file has more than 40000 line items, when ever i do any changes in the file its taking a long time to calculate, is there any option to improve the speed.

Counting unique values on large data sets is calculation intensive.

This array formula** should calculate faster:

=SUM(IF(FREQUENCY(IF(Sheet2!C1:C10="Completed",IF(Sheet2!B1:B10="CAR",MATCH(Sheet2!A1:A10,Sheet2!A1:A10,0))),ROW(Sheet2!A1:A10)-ROW(Sheet2!A1)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.