Help with Array Formula

[dlrollings83]

I have a table with a large amount of data and need help with some formula
Data:

014050	Street 1	N15 4HU	Haringey	Current	TELM	15/05/2018	Charlene
014050	Street 1	N15 4HU	Haringey	Current	TELX	09/05/2018	Tan
014050	Street 1	N15 4HU	Haringey	Current	TXTS	09/05/2018	Tan
014059	Street 2	N15 4HU	Haringey	Current	TELX	11/05/2018	Charlene
014059	Street 2	N15 4HU	Haringey	Current	TXTS	11/05/2018	Charlene
014589	Street 3	N15 4HZ	Haringey	Current	CTHB	01/05/2018	Dominic

<tbody>
</tbody>

Column A is Ref Number, B is Address, F is the work type, G is the date and H is the user (there are almost 20K rows of data)

I have entered an array formula that counts the new data in row A is it matchs row H as follows:
{=SUM(--(FREQUENCY(IF('Sheet 1'!H:H=A4,MATCH('Sheet 1'!A:A,'Sheet 1'!A:A,0)),ROW('Sheet 1'!A:A)-ROW('Sheet 1'!A3)+1)>0))}

This works fine for the total individual accounts that a person has looked at, but this data is going back a fair few months and there are 2 things I could use assistance with.

1. Anyway to get the same results (the above would return values of 2 for Charlene, 1 for Dominic and 1 for Tan) without using an array formula, my PC is slow enough without excel dragging its heels.

2. Anyway (with an array if required, but preferably not) for the above to look up the officer and count unique cells in column A when column G is within a set date range. Basically what I need is =if(and(G:G='Date Range',H:H="Officer Name')'count unique data'A:A)

Happy to consider a macro is that is easier.

I am going to start looking at Power Bi in the hope that that makes this analysis easier but I have only just started that journey so have no idea what I am doing on that yet.

Many Thanks in advance.

 

[Joyner]

Hello, I'm not sure I exactly follow, can you post how the above should look / the results you want.

But from what you said, I am thinking a simple Pivot Table may do it.

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H
2	14050	Street 1	N15 4HU	Haringey	Current	TELM	2018-05-15	Charlene
3	14050	Street 1	N15 4HU	Haringey	Current	TELX	2018-05-09	Tan
4	14050	Street 1	N15 4HU	Haringey	Current	TXTS	2018-05-09	Tan
5	14059	Street 2	N15 4HU	Haringey	Current	TELX	2018-05-11	Charlene
6	14059	Street 2	N15 4HU	Haringey	Current	TXTS	2018-05-11	Charlene
7	14589	Street 3	N15 4HZ	Haringey	Current	CTHB	2018-05-01	Dominic
Sheet 1

Book1
	A	B
1	2018-05-01
2	2018-05-31
3	name	# distinct
4	Charlene	2
5	Tan	1
6	Dominic	1
7
Sheet2

Avoid referencing whole columns for reasons of efficiency.

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

=SUM(IF(FREQUENCY(IF(('Sheet 1'!$A$2:$A$7<>"")*('Sheet 1'!$H$2:$H$7=$A4)*('Sheet 1'!$G$2:$G$7>=$A$1)*('Sheet 1'!$G$2:$G$7<=$A$2),MATCH('Sheet 1'!$A$2:$A$7,'Sheet 1'!$A$2:$A$7,0)),ROW('Sheet 1'!$A$2:$A$7)-ROW(INDEX('Sheet 1'!$A$2:$A$7,1,1))+1),1))