Array Formula with IF filter

Array formulas are useful for getting the result from a filtered list.

Continuing our series on Excel array formulas.

Here’s a list of stocks with industries aka Table3.  How many stocks are in the ‘Shop’ industry?

The array formula in G5 has the answer from the formula:


The expanded version of the array might help explain (select the ‘Table3[Industry]’ range then F9)

=COUNTIF({"Fizzy Drinks";"Fizzy Drinks";"Shop";"Shop";"Nerd Stuff";"Nerd Stuff";"Phones";"Phones";"Shop"},G4)

The array is the list of industries in column B.

So What?

Why does skipping a column matter?   It doesn’t, on such a small scale but expand that to much longer lists and worksheets and it makes a big difference.

Skipping unnecessary columns makes the overall worksheet easier to read, less clutter.

More important, array formulas are faster.  Excel keeps the array in memory which takes less time and CPU power to calculate.

CountIF() in an array is a lot faster than using VLOOKUP() , MATCH() or some other workarounds we’ve seen <g>.


Join Office for Mere Mortals today

Office for Mere Mortals is where thousands pick up useful tips and tricks for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  We've never spammed or sold addresses since we started over twenty years ago.
Invalid email address