COUNTIFS on range that is expanding more than 1k rows will get frozen :(

[dellzy]

Hi All,


The formula below works just great for me but the calculation will not move at all when the data rows is more than 1k. Can somebody help to amend the below code to allow me to have what I need regardless the data rows, please? This formula is actually calculates number of staff who access the same system on the same date. Please take note, the dates are not in chronological manner and not possible to change because it will impact other formula's output if done so. I supposed that would be one of the factors why the calculation will take longer than it should but still possible as long as less than 1k.


with Ctrl+Shift+Enter
=IF($J49="","",SUM(IFERROR(SIGN(LEN($J$2:$J$439))/COUNTIFS($J$2:$J$439,$J$2:$J$439&"",$U$2:$U$439,$U$2:$U$439,$U$2:$U$439,$U49),0)))


Or if anyone can advise me other mechanisms (other than manual count) then am open for suggestions.


Thank you in advance.


DZ

 

[Aladin Akyurek]

Care to word what this formula is supposed to do?

You could of course post 5 rows from the J range, 5 rows from the U range, and the expected result for this sample of records.

 

[dellzy]

Hi Aladin Akyurek,

You may refer to my previous post at http://www.mrexcel.com/forum/excel-...t-unique-values-based-multiple-criterias.html

Before then I was asking what formula to count unique values. After I got the formula, now am having problem the formula cannot handle the expanding data rows.

Thank you in advance.

DZ

 

[Aladin Akyurek]

Hi Aladin Akyurek,

You may refer to my previous post at http://www.mrexcel.com/forum/excel-...t-unique-values-based-multiple-criterias.html

Before then I was asking what formula to count unique values. After I got the formula, now am having problem the formula cannot handle the expanding data rows.

Thank you in advance.

DZ

After seeing that thread, I would like to repeat my request: Please post 5 rows from the J range, 5 rows from the U range, and the expected result for this sample of records.

 

[dellzy]

After seeing that thread, I would like to repeat my request: Please post 5 rows from the J range, 5 rows from the U range, and the expected result for this sample of records.

Here it is, Aladin Akyurek

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Col J[/TD]
[TD]Col U[/TD]
[TD]Expected Output[/TD]
[/TR]
[TR]
[TD]Shasi Kumar
Fadzli
Karthik
Shasi Kumar
Karthik
Shasi Kumar
Fadzli
Karthik
Shasi Kumar
Fadzli
Karthik
Shasi Kumar
Fadzli
[/TD]
[TD]7/3/2016
8/3/2016
8/3/2016
8/3/2016
9/3/2016
9/3/2016
10/3/2016
10/3/2016
10/3/2016
11/3/2016
11/3/2016
11/3/2016
12/3/2016
[/TD]
[TD]1
3
3
3
2
2
3
3
3
3
3
3
1
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Here it is, Aladin Akyurek

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Col J[/TD]
[TD]Col U[/TD]
[TD]Expected Output[/TD]
[/TR]
[TR]
[TD]Shasi Kumar
Fadzli
Karthik
Shasi Kumar
Karthik
Shasi Kumar
Fadzli
Karthik
Shasi Kumar
Fadzli
Karthik
Shasi Kumar
Fadzli[/TD]
[TD]7/3/2016
8/3/2016
8/3/2016
8/3/2016
9/3/2016
9/3/2016
10/3/2016
10/3/2016
10/3/2016
11/3/2016
11/3/2016
11/3/2016
12/3/2016[/TD]
[TD]1
3
3
3
2
2
3
3
3
3
3
3
1[/TD]
[/TR]
</tbody>[/TABLE]

In V2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF(1-($J$2:$J$14=""),
    IF($U$2:$U$14=$U2,MATCH($J$2:$J$14,$J$2:$J$14,0))),ROW($J$2:$J$14)-ROW($J$2)+1),1))

If you get into a performance problem with this set up, we can polish it for better speed.