Need formula help for counting Unique Text values with different date criteria

[JRCEI]

Hello,

I'm looking for a way to count unique text values using criteria in 2 columns.

MDU Name and Created Time

I currently have a formula to count the total unique number of MDU's it looks like this:

=SUM(--(FREQUENCY(IF('MDU DATA'!B:B<>"",MATCH('MDU DATA'!B:B,'MDU DATA'!B:B,0)),ROW('MDU DATA'!B:B)-ROW('MDU DATA'!B2)+1)>0))-1

Cell F3: Total MDU's 8

This is great and it works but I need to have it constrained by a date range. This date will always be referenced in cell: I7

I need another formula to count the total unique number of MDU's but needs 2 date constraints it must be >= cell C8 and <= cell I8

Cell F4: Active MDU's 1

I've attempted multiple times to include ifs to match based on the date ranges and I just couldn't get it to work with using the tidbits below.

I have been using this in my other formulas in order to put in the date ranges that I'm looking for.

'LEAD DATA'!$P:$P, ">="&C$7, 'LEAD DATA'!$P:$P, "<="&I$7) for a weekly view

or for a current date view:

'LEAD DATA'!$P:$P, "<="&I$12)

the thing is these have been used in countifs formula's and I can't figure out how to repurpose them into my sum formulas.




Here is my breakdown page where all the formulas are used and reflected.

	A	B	C	D	E	F	G	H	I	J	K
1		Total Calls	897		MDU's Acquired	6		Leads Acquired	16
2					Total MDU's	127		Current Bucket	24
3		Current Bucket	24		Active MDU's	30		Leads Sold	3
4		Leads Acquired	16		Active Rate			Sale Rate	13%
5		Leads Sold	3
6
7	WK	18​	4/25/2021	4/26/2021	4/27/2021	4/28/2021	4/29/2021	4/30/2021	5/1/2021
8
9		Acquisition Calls	Sun	Mon	Tue	Wed	Thur	Fri	Sat	Totals
10		Abbie	0	10	58	61	54	50	0	233
11		Bryan	0	22	23	34	43	0	0	122
12		Charlie	0	0	28	35	44	45	0	152
13		Denise	0	12	57	70	75	52	0	266
14		Eddie	0	0	23	32	36	33	0	124
15		Farrah	0	0	0	0	0	0	0	0

This is what my data sheet looks like the sheet is named 'MDU DATA'!

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Market

MDU Name

Office Street

Office City

Office State

Office Zip

Full Name

Created Time

Aquistion Rep ID

Occupancy Rate

Units

RPC

RPC Type

2

A

The Place

1 road rd

Anywhere

IL

10000​

Aaron Brown

2/26/2021​

JZuar

95​

234​

PREMIUM

Check

3

A

The Spot

3 place st

Somewhere

IL

10000​

Bobby Croy

3/2/2021​

CArce

95​

234​

PREMIUM

Check

4

A

The Building

2 happy dr

Someplace

IL

10000​

Carrie Dunn

3/1/2021​

CArce

99​

206​

PREMIUM

Check

5

A

The House

9 fine ave

Somespot

IL

10000​

Darius Evans

3/1/2021​

SYerk

92​

301​

PREMIUM

Check

6

A

The Condo

5 condo ln

Someland

IL

10000​

Eva Fae

3/1/2021​

SYerk

96​

362​

PREMIUM

Check

7

A

The Property

4 property dr

Somecity

IL

10000​

Frank Gundy

3/1/2021​

SYerk

96​

192​

PREMIUM

Check

8

A

The Place

1 road rd

Anywhere

IL

10000​

Aaron Brown

3/1/2021​

JZuar

95​

234​

PREMIUM

Check

9

A

The Place

1 road rd

Anywhere

IL

10000​

Aaron Brown

5/3/2021​

JZuar

95​

234​

PREMIUM

Check

10

A

The Johnsky

6 John ct

Somesea

IL

10000​

Gertrude Zing

3/8/2021​

CArce

100​

82​

PREMIUM

Check

I'm having a hard time figuring out how to find the unique count of MDU Names within date ranges found on the breakdown sheet above that are matched against column H on my MDU Data sheet.

Any and all assistance would be GREATLY appreciated! Thank you in advance.

JRCEI

 

[Marcelo Branco]

Maybe this...

=SUM(--(FREQUENCY(IF('MDU DATA'!B2:B1000<>"",IF('MDU DATA'!H2:H1000>=C7,IF('MDU DATA'!H2:H1000<=I7,MATCH('MDU DATA'!B2:B1000,'MDU DATA'!B2:B1000,0)))),ROW('MDU DATA'!B2:B1000)-ROW('MDU DATA'!B2)+1)>0))
Ctrl+Shift+Enter

By the away, for the sake of performance avoid references to entire columns in array formulas.

M.

 

[Momentman]

If use O365, then maybe a combination of UNIQUE and FILTER functions

iqcorr.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Market	MDU Name	Office Street	Office City	Office State	Office Zip	Full Name	Created Time	Aquistion Rep ID	Occupancy Rate	Units	RPC	RPC Type		Unique MDU
2	A	The Place	1 road rd	Anywhere	IL	10000	Aaron Brown	30-Apr-21	JZuar	95	234	PREMIUM	Check		The Place
3	A	The Spot	3 place st	Somewhere	IL	10000	Bobby Croy	28-Apr-21	CArce	95	234	PREMIUM	Check		The Spot
4	A	The Building	2 happy dr	Someplace	IL	10000	Carrie Dunn	05-May-21	CArce	99	206	PREMIUM	Check		The House
5	A	The House	9 fine ave	Somespot	IL	10000	Darius Evans	01-May-21	SYerk	92	301	PREMIUM	Check		The Condo
6	A	The Condo	5 condo ln	Someland	IL	10000	Eva Fae	28-Apr-21	SYerk	96	362	PREMIUM	Check
7	A	The Property	4 property dr	Somecity	IL	10000	Frank Gundy	05-May-21	SYerk	96	192	PREMIUM	Check
8	A	The Place	1 road rd	Anywhere	IL	10000	Aaron Brown	30-Apr-21	JZuar	95	234	PREMIUM	Check
9	A	The Place	1 road rd	Anywhere	IL	10000	Aaron Brown	02-May-21	JZuar	95	234	PREMIUM	Check
10	A	The Johnsky	6 John ct	Somesea	IL	10000	Gertrude Zing	02-May-21	CArce	100	82	PREMIUM	Check
MDU

Cell Formulas
Range	Formula
O2:O5	O2	=UNIQUE(FILTER($B$2:$B$10,($H$2:$H$10>=Breakdwon!$C$7)*($H$2:$H$10<=Breakdwon!$I$7)))
Dynamic array formulas.

 

[JRCEI]

Marcelo - thank you this worked for both things that I needed it for. much appreciated!

Momentman - this was a bit above me but I appreciate the information and will start looking into these formulas as well for future use and revamping my existing stuff.


Much appreciated!

 

[Marcelo Branco]

You're welcome. Glad to help

M.

 

[Peter_SSs]

Momentman - this was a bit above me but I appreciate the information and will start looking into these formulas as well for future use and revamping my existing stuff.

I'd strongly recommend you put a priority on that as you now have some much more powerful functions than in previous versions which result in much simpler formulas.

For example, instead of your current F3 formula to count unique MDU's, you could use this, assuming 1000 is greater than the number of rows of your data.

=ROWS(UNIQUE('MDU DATA'!B2:B1000))-1

.. and for your count unique in the date range, this slight adaptation of Momentman's suggestion

=IFNA(ROWS(UNIQUE(FILTER('MDU DATA'!B2:B1000,('MDU DATA'!H2:H1000>=C8)*('MDU DATA'!H2:H1000<=I8),NA()))),0)