Use array functions in Excel to perform multiple calculations in one cell

Posted on:  08/28/2017
Use array functions in Excel to perform multiple calculations in one cell

If you want to become a master of Excel, you need to learn how to use array formulas. Array formulas will perform calculations that other formulas in Excel can’t do.

Here is example 1 – multi-cell calculation to return multi results

  1. Select D2 to D8.
  2. Type =B2:B8*C2:C8.
  3. Press Ctrl + Shift + Enter on the keyboard.
  4. Click in cell D2 and look at your formula bar.

Array function example 1

An array function will have braces { } around it. You can not manually type the braces. You must use Ctrl + Shift + Enter.

Here is example 2 – multi-cell calculation to return one result

Using the same example from above, I want to know what is the grand total.

  1. Click in cell C10.
  2. Type =SUM(B2:B8*C2:C8).
  3. Press Ctrl + Shift + Enter on the keyboard.
  4. Click in C10 and look in the formula bar.

Array function

YouTube Video on Array formula

Shown in the video:

  1. Four array formulas including Frequency function.
  2. Countif
  3. Countifs
Chris Menard

Chris Menard is a certified Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. He runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events every year, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the Support Staff Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at chrismenardtraining.com/linked or watch his videos on YouTube at chrismenardtraining.com/youtube.

Categories