Array Formula with IF filter

Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.

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>.


Want More?

Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.