Array Function vs Countif and Countifs

by | Aug 28, 2017

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 - multi results in multi cells

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 functions

 

YouTube Video on Array formula

Shown in the video:

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

 

Online Excel Training with Chris Menard

Class is hosted with teachable.com http://chrismenardtraining.teachable.com

 

Corporate Trainer

Menard has been training corporate clients for over 20 years. His exercises are geared towards business clients. Exercises range from inventory, profit and loss (P&L), profit margins, quarterly data, employee data, analyzing customer data, HR exercises, yearly analysis, and other business related exercises.

Lifetime Investment

If you are here, you are most likely already an Excel user and will be using Excel the rest of your life. Why not invest in learning Excel the right way? Make yourself more proficient and productive in one of the most popular programs in the world.

24-7 Access

Once you buy the course, it is yours forever. You can take the course at work, come home and work on the course, work from your laptop, work from desktop, watch on tablet or even smartphone.

chris menard on Linkedinchris menard on Youtube
chris menard
Microsoft Office Master Instructor

I train corporate clients in Microsoft Office Excel, PowerPoint, Word, and Outlook. When I’m not training, I blog about technology and create training videos on YouTube. I currently have over 240 technology videos available. Most of the videos I’ve created come from questions asked during MS Office training.


Pin It on Pinterest