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 Microsoft Certified Trainer (MCT) and is employed full-time as a Trainer for BakerHostetler, one of the nation’s largest law firms. Menard has a YouTube channel with over 900 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 15 million viewers have appreciated. Menard also does public speaking at conferences for CPAs and Administrative Professionals. Connect with Chris on LinkedIn at chrismenardtraining.com/linked or on YouTube at chrismenardtraining.com/youtube

Categories