Get A Distinct Count Report With Dynamic Array Formulas!

In this video, you can learn how to use the UNIQUE and COUNTIF functions to count
unique rows based on multiple columns of data. This is the third video in a series
looking at solutions to a Data Analysis Challenge that I gave to viewers.
If you’d like to read the accompanying blog post on my website, you can access it here:

Related Videos:
How to Count Unique Rows with Power Query:
2 Ways to Calculate Distinct Count with Pivot Tables:
Excel Data Analysis Challenge - Count Transactions with Multiple Rows:
Dynamic Array Functions & Formulas that Spill:
Total Rows for Dynamic Array Formulas & Spill Ranges:

#MsExcel #ExcelCampus
00:00 Introduction
00:13 Distinct Count Report with Dynamic Array Formulas
01:04 Dynamic Array Formulas
02:03 The UNIQUE Function
03:56 The COUNTIF Function

[PaulEBrownbill]

Very interesting video that came through to me on the 'For you' tab. thanks Paul

[jeffdeutsch]

Wow... Never have seen spill ranges or that notation! Thanks!

[darrylmorgan]

Great Tutorial Using Dynamic Arrays, Really Nice To See The COUNTIF FUNCTION..Thank You Jon :)

[wayneedmondson]

HI John. Awesome.. as always! Thanks for the great tips and tricks in this video and in the series. Learned a lot and had fun too :)) Thumbs up!!

[doktorexcel]

Very nice and quite close to my solution. Thank you for this challenge.

[sujitkolhe]

Very helpful, I am one doubt if we have few blanks in source dada and when we apply pivot distinct count, it will also count blank cell. How to avoid blank cell count in this situation. Pls guide

[jorispinter]

Thank you Jon, for all the excellent info you provide.
The IFS functions (among others) only accept a range as input for the criteria range.

So how do I count distinct values when the input is an ARRAY, not a RANGE?

[rbspiritual]

Your all videos, content, qualities are very good a request you can you create a chennal where you answer our queries of any of your videos contant or trubalshoot or any live session in a week for this if you have convenience time for it. It will be greate for us.

[monikachauhan]

U mentioned that u may use the pivot to resolve this issue, cn u plz send the link fr dat, i cant find dat video n by the way thank u sooo mch

[jackjohnshei]

blank columns show 1 in excel distinct count...how to rectify this issue.if you make any video please sent the link.

[mustavogaia]

What is the equivalente to dynamic arrays in VBA? I mean, I am not sure if ".currentregion" works the same way.

[virginiaprovenza]

Hello - I've watched this video and several others multiple times, but can't figure out the best way to get the results I need. Can anyone help? Thank you.
I reworked the formula and see that I can't capture 2 columns of data. The Unique formula must not be what I need. I have been trying to figure this out for 2 days.
I have 1, 200 line items. I need to see how many are a specific Type, then Series Name, then the number of times held, etc.
Example: Column A = TYPE = lecture, conference, skills lab, education;
Column B = NAME = Cardiac Care, Crticial Care, Trauma, etc.;
Column C = DATE;
Column D = ATTENDEES;
In Col A there may be 24 conferences, 3 education Series but they are held a variety of ways - "A" is held every month; B every week and C every 2 weeks. How many total Series: 3; how many times held in a year for each series - A=12; B=52 and C = 24. This is so simple in my head but so difficult to figure out in excel.

[ayaanansari]

Asslam-o-Alaikum Sir please help me that how can check my Internet is Connect or Not Connect with the the help of VBA Code. Please reply me.

[SilentvoidRevolcan]

Apparently new functions like unique are "spill" aware - Any other functions which could take advantage of "spill"