Consulting

Results 1 to 6 of 6

Thread: #VALUE Error using Sumproduct

  1. #1
    VBAX Expert
    Joined
    Feb 2010
    Posts
    678
    Location

    #VALUE Error using Sumproduct

    The following formula has worked like a charm for several years:

    =CONFIG!$A$48-SUMPRODUCT(--(INDIRECT(Reconciled)=""),--(INDIRECT(AccountType)="Credit Card"),--(INDIRECT(Description)"Kohl's"),         INDIRECT(Expenses)-INDIRECT(Deposits))
    However, I changed it today to test for two conditions within the same column, as follows:

    =CONFIG!$A$48-SUMPRODUCT(--(INDIRECT(Reconciled)={"","C"}),--(INDIRECT(AccountType)="Credit Card"),--(INDIRECT(Description)"Kohl's"),         INDIRECT(Expenses)-INDIRECT(Deposits))
    The new formula is resulting in a #VALUE! error. However, not only does the first formula work fine, I can change it to test for "C" only and it also works fine. What am I overlooking?

    Thanks,

    Opv

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Can you post a sample workbook?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    VBAX Expert
    Joined
    Feb 2010
    Posts
    678
    Location
    Quote Originally Posted by mdmackillop View Post
    Can you post a sample workbook?
    Alas, I am uncomfortable doing that as there is nothing in this workbook but confidential information. I am able to get around the issue by revising my formula to include two sumproduct statements rather than trying to combine them into one. I just left the thread as unsolved on the chance that something might jump out at someone. I'll mark the post as solved. Thanks.

  4. #4
    VBAX Expert
    Joined
    Feb 2010
    Posts
    678
    Location
    I'm still using the work-around. However, I happened across another occurrence of the same issue and thought I'd post it just to shed more light on what might be going on.

    =SUMPRODUCT(--(INDIRECT(AccountType)<>"Withholding") )
    This returns a value.

    =SUMPRODUCT(--(INDIRECT(Categories)={"Transfer","Pets"})  )
    This returns a value.

    =SUMPRODUCT(--(INDIRECT(AccountType)<>"Withholding"), --(INDIRECT(Categories)={"Transfer","Pets"})  )

    This results in a #Value! error. Also, each individual function, when entered as a stand-alone statement, returns a higher number of occurrences than there are possible rows in the defined array.

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,095
    Location
    Try

    =CONFIG!$A$48-SUMPRODUCT(--((INDIRECT(Reconciled)="")+(INDIRECT(Reconciled)="C")),--(INDIRECT(AccountType)="Credit Card"),--(INDIRECT(Description)="Kohl's"),INDIRECT(Expenses)-INDIRECT(Deposits))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    VBAX Expert
    Joined
    Feb 2010
    Posts
    678
    Location
    Thank you. That seems to work.

Posting Permissions

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