I need to know about array formula in Excel

Asked By 40 points N/A Posted on -
qa-featured
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?

 

SHARE
Best Answer by Neil Auso
Answered By 5 points N/A #200197

I need to know about array formula in Excel

qa-featured

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:

Screen Shot 2014-12-23 at 3.59.49.png


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.

Best Answer
Best Answer
Answered By 0 points N/A #200196

I need to know about array formula in Excel

qa-featured

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. 

 

Related Questions