MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.