+ Reply to Thread
Results 1 to 4 of 4

Adjusting an array formula to not count blank spaces.

  1. #1
    Registered User
    Join Date
    02-26-2016
    Location
    Cairns, Australia
    MS-Off Ver
    2010
    Posts
    15

    Adjusting an array formula to not count blank spaces.

    OK so I have added a code in VBA to the workbook to support this formula:

    =SUM(IF(FREQUENCY(IF(V(arrayunion('JAN'!C3:C36,'FEB'!C6:C36,'MAR'!C6:C36))<>"",MATCH(V(),V(),0)),ROW(INDIRECT("1:"&COLUMNS(V())))),1))

    This formula is for a Quarterly report that gives only the individual residents for the 3 month period (each month is on a separate worksheet) So if someone stayed in January and again in March, they should only be counted once.

    This formula is returning a count of 1 more than the actual number of individuals. Is it possible that it is counting blank spaces as 1? If so how do I fix the formula?
    Cheers.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Adjusting an array formula to not count blank spaces.

    How about:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    02-26-2016
    Location
    Cairns, Australia
    MS-Off Ver
    2010
    Posts
    15

    Re: Adjusting an array formula to not count blank spaces.

    Oh this is a silly mistake. It is a typo. the formula is searching worksheet jan! C3:C36. It should be C6:C36.
    Thanks anyway.

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Adjusting an array formula to not count blank spaces.

    Yeah, I wondered why it would always give one more

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula to delete any blank spaces in a UPC code
    By plank22 in forum Excel General
    Replies: 1
    Last Post: 11-26-2015, 01:20 PM
  2. Count blank and non-blank entrys in an array
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-23-2014, 09:34 AM
  3. [SOLVED] Formula Array not adjusting to range
    By LightHeart7 in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 11-22-2013, 01:56 PM
  4. Replies: 3
    Last Post: 01-14-2008, 11:32 PM
  5. Replies: 6
    Last Post: 08-15-2007, 09:14 AM
  6. LOOKUP won't count blank spaces
    By aphid in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-29-2007, 05:55 PM
  7. Adjusting a formula to return Blank instead of a zero
    By Rashkumar in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 02-21-2007, 11:22 AM

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