Sumproduct - Returning #value

[Mokolpop]

Hi, As I deal with lots of data with multiple identfiers in my cost models I generally use Sumproduct and have done for some time.

I have an issue though which i think I have identified as one of the labels in the Sumproduct formula that returns the #value.

Formula is =SUMPRODUCT(($B$4=BO_GBP_BATCH)*($B5=BO_RBA_TAG)*(C$4=BO_GBP_YEAR)*BO_TOTAL)

2 identifiers Batch and Tag, 1 profile identifer Year and then the Total at the end.

2500 Rows and 160 columns

Each of the labels used starts and finishes on the same rows and the profile and total labels start at the same column and end at the same column.

If I simply reference the =BO_TOTAL it returns the total sum

If I do the above formula but dont profile it and simply use the Totals columnI have created it returns the number.

Im puzzled never had an issue with Sumproduct before and if it returned an error ive been able to locate it

Can anyone help me please ?

A Puzzled fan of Sumproduct

 

[BrianMH]

Not sure I understand what you are doing here. You have a sumproduct with only a single array in it (there are no separating commas). Also generally when using true/false you need to convert them to a number by doing --() such as --($B$4=BO_GBP_BATCH).