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

JRCEI

New Member
Joined
Mar 24, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.


ABCDEFGHIJK
1Total Calls897MDU's Acquired6Leads Acquired16
2Total MDU's127Current Bucket24
3Current Bucket24Active MDU's30Leads Sold3
4Leads Acquired16Active RateSale Rate13%
5Leads Sold3
6
7WK
18​
4/25/20214/26/20214/27/20214/28/20214/29/20214/30/20215/1/2021
8
9Acquisition CallsSunMonTueWedThurFriSatTotals
10Abbie010586154500233
11Bryan02223344300122
12Charlie00283544450152
13Denise012577075520266
14Eddie00233236330124
15Farrah00000000



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

ABCDEFGHIJKLM
1MarketMDU NameOffice StreetOffice CityOffice StateOffice ZipFull NameCreated TimeAquistion Rep IDOccupancy RateUnitsRPCRPC Type
2AThe Place1 road rdAnywhereIL
10000​
Aaron Brown
2/26/2021​
JZuar
95​
234​
PREMIUMCheck
3AThe Spot3 place stSomewhereIL
10000​
Bobby Croy
3/2/2021​
CArce
95​
234​
PREMIUMCheck
4AThe Building2 happy drSomeplaceIL
10000​
Carrie Dunn
3/1/2021​
CArce
99​
206​
PREMIUMCheck
5AThe House9 fine aveSomespotIL
10000​
Darius Evans
3/1/2021​
SYerk
92​
301​
PREMIUMCheck
6AThe Condo5 condo lnSomelandIL
10000​
Eva Fae
3/1/2021​
SYerk
96​
362​
PREMIUMCheck
7AThe Property4 property drSomecityIL
10000​
Frank Gundy
3/1/2021​
SYerk
96​
192​
PREMIUMCheck
8AThe Place1 road rdAnywhereIL
10000​
Aaron Brown
3/1/2021​
JZuar
95​
234​
PREMIUMCheck
9AThe Place1 road rdAnywhereIL
10000​
Aaron Brown
5/3/2021​
JZuar
95​
234​
PREMIUMCheck
10AThe Johnsky6 John ctSomeseaIL
10000​
Gertrude Zing
3/8/2021​
CArce
100​
82​
PREMIUMCheck


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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.
 
Upvote 0
Solution
If use O365, then maybe a combination of UNIQUE and FILTER functions

iqcorr.xlsx
ABCDEFGHIJKLMNOP
1MarketMDU NameOffice StreetOffice CityOffice StateOffice ZipFull NameCreated TimeAquistion Rep IDOccupancy RateUnitsRPCRPC TypeUnique MDU
2AThe Place1 road rdAnywhereIL10000Aaron Brown30-Apr-21JZuar95234PREMIUMCheckThe Place
3AThe Spot3 place stSomewhereIL10000Bobby Croy28-Apr-21CArce95234PREMIUMCheckThe Spot
4AThe Building2 happy drSomeplaceIL10000Carrie Dunn05-May-21CArce99206PREMIUMCheckThe House
5AThe House9 fine aveSomespotIL10000Darius Evans01-May-21SYerk92301PREMIUMCheckThe Condo
6AThe Condo5 condo lnSomelandIL10000Eva Fae28-Apr-21SYerk96362PREMIUMCheck
7AThe Property4 property drSomecityIL10000Frank Gundy05-May-21SYerk96192PREMIUMCheck
8AThe Place1 road rdAnywhereIL10000Aaron Brown30-Apr-21JZuar95234PREMIUMCheck
9AThe Place1 road rdAnywhereIL10000Aaron Brown02-May-21JZuar95234PREMIUMCheck
10AThe Johnsky6 John ctSomeseaIL10000Gertrude Zing02-May-21CArce10082PREMIUMCheck
MDU
Cell Formulas
RangeFormula
O2:O5O2=UNIQUE(FILTER($B$2:$B$10,($H$2:$H$10>=Breakdwon!$C$7)*($H$2:$H$10<=Breakdwon!$I$7)))
Dynamic array formulas.
 
Upvote 0
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!
 
Upvote 0
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.
Excel Formula:
=ROWS(UNIQUE('MDU DATA'!B2:B1000))-1

.. and for your count unique in the date range, this slight adaptation of Momentman's suggestion
Excel Formula:
=IFNA(ROWS(UNIQUE(FILTER('MDU DATA'!B2:B1000,('MDU DATA'!H2:H1000>=C8)*('MDU DATA'!H2:H1000<=I8),NA()))),0)
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top