MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Other Functions Can Now Accept Arrays as Arguments


September 19, 2019 - by Bill Jelen

Other Functions Can Now Accept Arrays as Arguments. Photo Credit: Antonio Garcia at Unsplash.com

Once you see the list of genres, you might want to know how frequently each genre appears. That would normally require a series of COUNTIF or COUNTIFS formulas. For example, =COUNTIF(D$4:D$6132,"Rock") would count how many songs were in the Rock genre. But rather than entering a bunch of COUNTIF functions, you could enter a single COUNTIF function and pass an array as the second argument.


The formula below uses The Spiller syntax to ask Excel to repeat the COUNTIF for each answer in the UNIQUE function in F5.

The UNIQUE function is returning a list of Genres starting in F5. You want to use COUNTIF to count how many songs there were in each genre. But you have no idea how many rows there might be. A single formula of =SUMIF(D4:D6132,F5#) will return as many rows as were returned by the dynamic array in F5.

Title Photo: Garcia at Unsplash.com


Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.