Multiple criteria Unique Counting

Please see if this thread here --

http://216.92.17.166/board2/viewtopic.php?t=62051

might be of use.

Almost, except I need to use the second option on this post - other people will be accessing this report without the morefunc add-in.
I can't get the formula in the second option to work with my code.
My data is as follows:

My summary sheet is like this:

As you can see, I have tried the SUMPRODUCT option (D4), but am not sure how to alter the other thread's option (C4).

Any help/time spent on this is appreciated.

In your sumproduct forrmula, change the comma to an asterisk prior to the column C bit -- your column C data in non-numeric, and must be coerced into 0/1 by * rather than the native syntax of "," --

as in =SUMPRODUCT((....*(Data!C3

have to work, but will try to post back w/ more.

It would have been helpful if you also provided the expected outcomes...

Formulas...

C4, which is copied across:

=SUM(IF(FREQUENCY(IF((Data!$A$3:$A$18=C$3)*(Data!$B$3:$B$18=$A4),MATCH(Data!$C$3:$C$18,Data!$C$3:$C$18,0),""),IF((Data!$A$3:$A$18=C$3)*(Data!$B$3:$B$18=$A4),MATCH(Data!$C$3:$C$18,Data!$C$3:$C$18,0),""))>0,1))

D4, which is also copied across:

=SUM(IF(FREQUENCY(IF((Data!$A$3:$A$18=C$3)*(Data!$B$3:$B$18=$A4),Data!$D$3:$D$18),IF((Data!$A$3:$A$18=C$3)*(Data!$B$3:$B$18=$A4),Data!$D$3:$D$18))>0,1))

Both formulas need to be confirmed with control+shift+enter, not just with enter.

Select C4:E5 and copy down.

Thanks for the help it is now working well.
I can understand that for 20,000 rows it will take some time to compute the formulas (my summary array is approx 80 x 30 cells)
I think I'll leave my computer running over the weekend & hope that by Monday morning all the figures are there!
Still, its a darn sight quicker than me calculating it manually!

barkley said:

Thanks for the help it is now working well.
I can understand that for 20,000 rows it will take some time to compute the formulas (my summary array is approx 80 x 30 cells)
I think I'll leave my computer running over the weekend & hope that by Monday morning all the figures are there!
Still, its a darn sight quicker than me calculating it manually!

Click to expand...

If the data in Data is sorted say on the first column, we can speed up the calculations.

I can sort it by the first column, then the second. Does this make it quicker automatically or will I need to change the code?

barkley said:

I can sort it by the first column, then the second. Does this make it quicker automatically or will I need to change the code?

Click to expand...

The exhibit below shows how the array-formulas can be restricted to apply to relevant subranges, enabled by the sorted data...

First define BigStr...

Activate Insert|Name|Define.
Enter BigStr as name in the Names in Workbook box.
Enter the following in the Refers to box:

9.99999999999999E+307

Click OK.

Formulas:

C2, which is copied across to E2..

=MATCH(C3,Data!$A:$A,0)

Row 2 must all names in alphabetical order.

The last cell in row 2 (in the above exhibit, it's F2) must house:

=MATCH(BigStr,Data!A:A)

Array-formulas:

C4, which is copied across:

=SUM(IF(FREQUENCY(IF((OFFSET(Data!$A$1,C$2-1,0,D$2-C$2,1)=C$3)*(OFFSET(Data!$B$1,C$2-1,0,D$2-C$2,1)=$A4),MATCH(OFFSET(Data!$C$1,C$2-1,0,D$2-C$2,1),OFFSET(Data!$C$1,C$2-1,0,D$2-C$2,1),0),""),IF((OFFSET(Data!$A$1,C$2-1,0,D$2-C$2,1)=C$3)*(OFFSET(Data!$B$1,C$2-1,0,D$2-C$2,1)=$A4),MATCH(OFFSET(Data!$C$1,C$2-1,0,D$2-C$2,1),OFFSET(Data!$C$1,C$2-1,0,D$2-C$2,1),0),""))>0,1))

D4, which is copied across:

=SUM(IF(FREQUENCY(IF((OFFSET(Data!$A$1,C$2-1,0,D$2-C$2,1)=C$3)*(OFFSET(Data!$B$1,C$2-1,0,D$2-C$2,1)=$A4),OFFSET(Data!$D$1,C$2-1,0,D$2-C$2,1)),IF((OFFSET(Data!$A$1,C$2-1,0,D$2-C$2,1)=C$3)*(OFFSET(Data!$B$1,C$2-1,0,D$2-C$2,1)=$A4),OFFSET(Data!$D$1,C$2-1,0,D$2-C$2,1)))>0,1))

Select C4:D5 and copy down.

Thank you Aladin for all your help.
I will give this a go on Monday morning and let you know how I get on.
Regards,
Darren