Look Who it Was Random video

Mr Excel & excelisfun Trick 167: Are All the Numbers 1 to 99 in Range? Array Formula or VBA?

4,737 views · Published 31 October 2014 · 16:17 · Indexed 20 October 2026

Channel: excelisfun · 2014 · Education

Watch on YouTube

Download Files: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/MrExcelExcelisfunTrick167.xlsm

Download workbook: http://people.highline.edu/mgirvin/ExcelIsFun.htm
Bill Mr Excel Jelen and Mike excelisfun Girvin show how to find out if the Numbers 1 to 99 are in a range:
1) (00:43 min mark) Overview
2) (01:10 min mark) Mr Excel: COUNTIF and an OR Criteria Function Argument Operation created with ROW function inside the MIN function and the keyboard Ctrl + Shift + Enter.
3) (04:13 min mark) Explaining how Function Argument Array Operation works.
4) (06:23 min mark) Excelisfun: COUNTIF and an OR Criteria Function Argument Operation created with an Array Constant (also ROW and INDIRECT functions) inside the MIN function and NO special keyboard Ctrl + Shift + Enter necessary.
5) (08:23 min mark) Mr Excel uses MATCH function and an Array Constant Function Argument Array Operation. NO special keyboard Ctrl + Shift + Enter necessary.
6) (10:03 min mark) Excelisfun: FREQUENCY function? No. COUNTIF and an OR Criteria Function Argument Operation created with an ROW and INDIRECT functions inside the AGGREGATE function and NO special keyboard Ctrl + Shift + Enter necessary.
7) (11:35 min mark) Mr Excel: VBA to show text “All There” or list the missing values.
8) (14:38 min mark) Excelisfun: IF function and AGGREGATE to avoid any special keystroke.

Dueling Excel

More from this channel