Array Formula with IF filter


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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?

img 5bc4973716011 - Array Formula with IF filter

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.

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

 

subs profile e1563205311409 - Array Formula with IF filter
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address