#VALUE Error using Sumproduct

[Opv]

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

[mdmackillop]

Can you post a sample workbook?

[Opv]

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.

[Opv]

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.

[Bob Phillips]

Try

=CONFIG!$A$48-SUMPRODUCT(--((INDIRECT(Reconciled)="")+(INDIRECT(Reconciled)="C")),--(INDIRECT(AccountType)="Credit Card"),--(INDIRECT(Description)="Kohl's"),INDIRECT(Expenses)-INDIRECT(Deposits))

[Opv]

Thank you. That seems to work.