SUMPRODUCT is driving me insane

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,868
Office Version
  1. 365
Platform
  1. Windows
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.

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

Address range: =Data!$X$2:$X$3729 o_O
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? :oops:
Unfortunately I can't upload the sheet as I'm working on a restricted PC, won't allow downloads.

Help very greatly appreciated.

Jon :(
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,868
Office Version
  1. 365
Platform
  1. Windows
Thanks for the reply Aladin. No, not one error, this is the only formulae generating errors. I checked using F5 Special.
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,868
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
noj said:
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,868
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi,

It's 3728.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
noj said:
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,868
Office Version
  1. 365
Platform
  1. Windows
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 :oops:
 

Forum statistics

Threads
1,147,846
Messages
5,743,515
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top