PDA

View Full Version : [SOLVED] #VALUE Error using Sumproduct



Opv
06-20-2016, 04:27 PM
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
07-12-2016, 01:15 PM
Can you post a sample workbook?

Opv
07-12-2016, 05:51 PM
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
08-24-2016, 11:07 AM
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
10-04-2016, 07:11 AM
Try


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

Opv
10-04-2016, 04:41 PM
Thank you. That seems to work.