SUMPRODUCT is driving me insane

[Jon von der Heyden]

Hi there,

I have used a series of sumproducts and I've never encountered this. The sumproduct is drivin by two criteria and finally adds up a tax amount.

=SUMPRODUCT(--(Address=$D62),--(Mapping_Period=F$7),Tax_Amount)

Address range: =Data!$X$2:$X$3729
Mapping_Period: =Data!$T$2:$T$3729
Tax_Amount: =Data!$S$2:$S$3729

Address is a 5 digit code, all are recognised values.
Mapping_Period is alpha-numeric (e.g. q102).
Tax_Amount is in currency, all are recognised values.

My formulae are spitting out #VALUE!. Any idea why?
Unfortunately I can't upload the sheet as I'm working on a restricted PC, won't allow downloads.

Help very greatly appreciated.

Jon

 

[Aladin Akyurek]

Do you have any #VALUE! error in those ranges?

You're using the comma syntax which ordinarily ignores text values in the range to sum, unless this contains just text and no numbers.

 

[Jon von der Heyden]

Thanks for the reply Aladin. No, not one error, this is the only formulae generating errors. I checked using F5 Special.

 

[daniels012]

Usually you get a #value! error when things don't match in the sumproduct formula.

For example:

__A_B_C_D
1_4_3_5_2
2_3_5_2_Empty
3_5_2_3_8
4_2_6_2_2

If you used sum product, the array has nothing to match in cell D2, therefore giving you an error.

Hope this Helps,
Michael

 

[Jon von der Heyden]

Thanks, but I've carefully checked and I don't find this to be the case. Even when checking each argument using the formula pallet, each argument presents the correct result, so I cannot narrow down the error.

Address range: {0,1,0,0,0,0,1..........}
Mapping_Period: {0,0,0,1,0,1,1........}
Tax_Amount: {131250,129507.........}

I'm baffled, might try starting from scratch.
Thanks,
Jon

 

[Aladin Akyurek]

Thanks, but I've carefully checked and I don't find this to be the case. Even when checking each argument using the formula pallet, each argument presents the correct result, so I cannot narrow down the error.

Address range: {0,1,0,0,0,0,1..........}
Mapping_Period: {0,0,0,1,0,1,1........}
Tax_Amount: {131250,129507.........}

I'm baffled, might try starting from scratch.
Thanks,
Jon

What is the result of:

=COUNT(Data!$S$2:$S$3729)

 

[Jon von der Heyden]

Hi,

It's 3728.

 

[Aladin Akyurek]

Hi,

It's 3728.

This doesn't make sense. I hoped you'd say 0 which would explain the #VALUE error. If you want me to look at the file, drop me a line via a PM.

 

[fairwinds]

I wold try:

=SUMPRODUCT(--(Data!$X$2:$X$3729 =$D62),--(Data!$T$2:$T$3729=F$7),Data!$S$2:$S$3729)

to make sure there are no spelling differences or simmilar mistakes.

 

[Jon von der Heyden]

Aladin, I'm so sorry, I'm an idiot. I found a #VALUE! in the address column. I used F5 special yesterday and filled the errors with color but didn't pick up on the error in this column - I didn't browse carefully enough.

Sorry