I need to know about array formula in Excel

Asked By beatricegvavra 40 points N/A Posted on - 12/23/2014

What is an array formula in Excel? I need to count the number of times the value in a cell is greater than 0 for a particular row or column. How do I use an array formula for this? Can I use the COUNT function? Is there any other function that I can use?

 

• Status: Open
• Question Views: 705
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Neil Auso

Go To Solution

Answered By Leon Fra 5 points N/A #200197

I need to know about array formula in Excel

Hi

Since you are seeking results based on a single criteria (“count the number of times the value in a cell is greater than 0 for a particular row or column”), the COUNTIF function could be used. You would highlight the range (rows and columns) and use the following formula: =COUNTIF(A1:C3,">0")

See picture below for illustration:

If you were using more than one criteria, the array function would be needed. An array formula simplifies several Excel calculations into one cell. This means, that complex related to

• Counting characters in a select cell range

• Sum of numbers that meet a criteria

• Sum of nth value from a selection (range)  of values.

Array formulas are also known as "CSE formulas," i.e. the CTRL+SHIFT+ENTER keys that are pressed to activate the function in your workbook (COMMAND+SHIFT+RETURN on MacIntosh systems). In Microsoft Excel, the curly brackets {} are automatically entered when you select the CSE keys.

About Leon Fra

Questions
0

Answers
10

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 12/23/2014
• Question Category: MS Excel

Best Answer

Best Answer

Answered By Neil Auso 0 points N/A #200196

I need to know about array formula in Excel

Array formulas is one of the powerful tool in Excel sheet. It mainly works with array, series of data or multiple data values and is not working for the single data value. Some of the array formula's we have SUM, AVERAGE or COUNT.

Yes, you can use the Count function for this. 

 

About Neil Auso

Questions
0

Answers
117

Best Answers
11

• Vote Up 0 Vote Down

• Posted on - 12/30/2014
• Question Category: MS Excel