Excel 2020: Other Functions Can Now Accept Arrays as Arguments

September 24, 2020 - 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

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.