Excel Array formulas are useful for getting the result from a filtered list quickly with a single, simple formula.
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:
=COUNTIF(Table3[Industry],G4)
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 which CountIf() filters to just the “Shop” results.
So What?
CountIF() in an array is a lot faster than using VLOOKUP() , MATCH() or some other workarounds we’ve seen <g>.
The formula is a lot faster than making a separate list the counting. It doesn’t matter a lot, on such a small scale but expand that for 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.
Excel Array formulas for everyone
