Link to home
Start Free TrialLog in
Avatar of WeThotUWasAToad
WeThotUWasAToad

asked on

Understanding Excel array formulas & a simple example

Hello,

I'm trying to combine two simple Excel (2013) formulas into a single array formula.

I also want to obtain a better overall understanding of Excel array formulas because I have a hunch there's more opportunities than I realize when using an array formula would be simpler and save time.

Another significant reason — which I acquired just this evening — comes from one of the sites I found while googling this topic. It says:
"To become an Excel power user, you need to know how to use array formulas…"
I definitely want to be an "Excel power user" because, well...I mean, it just sounds cool!   [Sorry, couldn't resist.]  :)

1st question
I came across the following definition:
"An array formula is a formula that can perform multiple calculations on one or more of the items in an array."
Is that an accurate and complete definition or would you modify it in some way?

2nd question
Suppose you have a spreadsheet with entries in the range B5:B16 as shown in the following screenshot (Fig. 1). And suppose you want a formula to reside in cell B2 which indicates the number of cells in that range which have entries:

User generated imageThe only method I currently know for doing that requires two steps:

Step 1 is to add a helper column (column C in Fig. 2) which contains a formula to ask whether or not content is present in each row.

User generated imageStep 2 is to use the function COUNTIF() (cell C18 in Fig. 3) to display how many cells in the helper column indicate that an entry is present in the adjacent column B range:

User generated imageIs this the kind of situation where an array formula in cell B2 could display the solution without requiring a helper column?

If so, what is the formula and how do you combine the two non-array formulas into it?

Thanks
SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of WeThotUWasAToad
WeThotUWasAToad

ASKER

Thank you very much. Every response is extremely helpful.