Results 1 to 18 of 18

Array formula counting empty cells as zero

Threaded View

  1. #1
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    1,007

    Array formula counting empty cells as zero

    Hi. I have this array formula

    =MAX(FREQUENCY(IF(ALL!$I$2:$I$5375="League",IF(ALL!$E$2:$E$5375="H",IF(ALL!$L$2:$L$5375=0,ROW(ALL!$L$2:$L$5375)))),IF(ALL!$I$2:$I$5375="League",IF(ALL!$E$2:$E$5375="H",IF(ALL!$L$2:$L$5375>0,ROW(ALL!$L$2:$L$5375))))))

    which works fine up to a point. It's looking for the longest sequence of zero's. However the formula is counting blank cells as zero's so as there is more data to be added each week and there are more rows left to be filled then the formula is counting these empty cells as zero's which is some cases produces a false result. To stop this happening I have to limit the range of the formula to the last row where data has been entered. Is there a way round this or does it mean every week I have to update the array formula? Many thanks.
    Last edited by Marvo; 12-04-2020 at 01:58 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Not counting cells when empty
    By andynw1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-25-2020, 01:22 PM
  2. [SOLVED] counting cells with an array formula in
    By swfarm in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-17-2019, 05:26 PM
  3. Replies: 8
    Last Post: 07-30-2018, 07:06 PM
  4. [SOLVED] Average array formula to conitinue calculating even if one of cells or many are empty
    By wellous in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-28-2015, 12:17 PM
  5. [SOLVED] Counting blank cells that are empty buy contain a formula...
    By Losguapos1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-29-2014, 11:15 AM
  6. counting empty cells
    By Davycc in forum Excel General
    Replies: 5
    Last Post: 04-07-2011, 12:00 PM
  7. Sumproduct or array formula for counting criteria of sum of cells
    By jasoncw in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-09-2007, 03:09 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1