Excel Array Formulas

I just found a feature of Excel that I’ve never heard of before: Array Formulas!

Essentially, you can use ranges in criteria of functions such as COUNTIF. Full details available: http://www.cpearson.com/excel/ArrayFormulas.aspx

I used it to find the number of distinct values in a column (http://www.cpearson.com/excel/Duplicates.aspx).

All you need in the formula is:


The curly braces around the formula make this an array formula.

I reckon I could do some pretty fancy processing using this in the future.. Not sure what though.


  1. Good find, I hate Excel and try and avoid it at all times, but I’ll keep this in mind. Cheers.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: