Get A Distinct Count Report With Dynamic Array Formulas!

Рет қаралды 18,618

Күн бұрын

Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
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:
www.excelcampus.com/functions...
Related Videos:
How to Count Unique Rows with Power Query:
• How to Count Unique Ro...
2 Ways to Calculate Distinct Count with Pivot Tables:
www.excelcampus.com/pivot-tab...
Excel Data Analysis Challenge - Count Transactions with Multiple Rows:
• Excel Data Analysis Ch...
Dynamic Array Functions & Formulas that Spill:
• Dynamic Array Function...
Total Rows for Dynamic Array Formulas & Spill Ranges:
• Get Dynamic Array Form...
#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

Пікірлер: 24

@PaulEBrownbill 3 ай бұрын

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

@jeffdeutsch 2 жыл бұрын

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

@darrylmorgan 3 жыл бұрын

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

@ExcelCampus 3 жыл бұрын

Glad you liked it! 😊

@DougHExcel 3 жыл бұрын

nice overview of UNIQUE!

@jarotization 3 жыл бұрын

Inspiring tutorial, thanks Jon

@ExcelCampus 3 жыл бұрын

Thanks! 🙂

@wayneedmondson1065 3 жыл бұрын

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!!

@doktorexcel8603 3 жыл бұрын

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

@ExcelCampus 3 жыл бұрын

Awesome! Thanks! 🙂

@joris3pinter 3 жыл бұрын

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?

@hw7channel571 3 жыл бұрын

Great video sir

@bador28 3 жыл бұрын

It is a nice idea

@humphreywu9039 3 жыл бұрын

Good video.

@ExcelCampus 3 жыл бұрын

Thanks Pret! 🙂

@orelshema911 3 жыл бұрын

Cool!

@sujitkolhe1298 3 ай бұрын

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

@rbspiritual2181 3 жыл бұрын

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.

@monikachauhan2674 3 жыл бұрын

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

@mustavogaia2655 3 жыл бұрын

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

@jackjohnshei8374 2 жыл бұрын

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

@ayaanansari4765 3 жыл бұрын

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.

@virginiaprovenza4510 2 жыл бұрын

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.