+ Reply to Thread
Results 1 to 3 of 3

Array formula issue:

  1. #1
    Registered User
    Join Date
    03-15-2010
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    72

    Array formula issue:

    I'm having issues with an array formula:

    Please Login or Register  to view this content.
    Assume:
    Z4 = Product 1
    AA3 = CC1

    If these values are both found, it returns the correct value/total I need. my problem arises when the values searched for aren't in the arrays. according to the coding, it should return "". currently it is returning a value of 0.

    if i change the formula to:
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    it still returns a value of 0 not the 'none' or '1' values I'd expect.

    if I change the code to:
    Please Login or Register  to view this content.
    I get a completely random seeming value. i.e. not 1 and not 0 as expected.

    any suggestions would be appreciated.

    r/

    SUPPO

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Array formula issue:

    according to the coding, it should return ""
    No, it should not. I think there may be some misunderstanding about how this array formula works.

    =sum(if(condition,range,""))

    If the condition is not met, the empty string is returned to the SUM function, which will return a zero.

    The SUM function will always return a number. If the range it sums is empty, the number will be zero.

    Now can work out how you want to process that zero. You could format it away or wrap the array formula into an IF statement that does something with the zero.

    cheers, teylyn

  3. #3
    Registered User
    Join Date
    03-15-2010
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Array formula issue:

    ah. that makes sense. I appreciate the information. Looking at it now, I can see it's returning a blank string to the SUM function, which as you indicated is treating it as a number not a null value and will display that in the output. luckily for me, this formula was an intermediary step and I found a formula that converts any 0 values to "" when the final display is shown.

    thanks again for the clarification.

    r/

    SUPPO

+ 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. Array Formula Issue - Need Help
    By igotgame in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-25-2016, 01:14 PM
  2. Array Formula Issue
    By FWM in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-18-2015, 08:57 AM
  3. [SOLVED] Issue with formula array
    By purlo in forum Excel General
    Replies: 9
    Last Post: 06-08-2015, 03:26 PM
  4. SUM ARRAY Formula Issue - Probably an easy one...
    By huntethic in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-10-2015, 12:27 PM
  5. Array formula issue
    By dushtin in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-27-2013, 11:53 AM
  6. Array / Concatenate formula issue
    By Ozwilly in forum Excel General
    Replies: 6
    Last Post: 10-27-2011, 04:41 AM
  7. [SOLVED] Tricky array formula issue - Using array formula on one cell, then autofilling down a range
    By aspenbordr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2005, 11:05 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