# Question on SUMPRODUCT

#### Pauljj

##### Well-known Member
I am quite confused, I am using a formula which works ok in once cell but when copied down one cell returns a #NA

This is my example:
Gallileo Lesiure.xls
JKLM
1025/06/20061288.71
1102/07/2006#N/A
1209/07/2006#N/A
1316/07/2006#N/A
Data

Can anyone see why this would happen?

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

##### MrExcel MVP
\$B10

in

=IF(SUMPRODUCT((B\$10:B\$700>\$J10)*(B\$10:B\$700<=\$J11)*(F\$10:F\$700))=0,"",SUMPRODUCT((B\$10:\$B\$700>\$J10)*(\$B10:B\$700<=\$J11)*(F\$10:F\$700)))

needs to be locked as: B\$10.

BTW, why don't you just invoke:

=SUMPRODUCT(--(B\$10:\$B\$700>\$J10),--(B\$10:B\$700<=\$J11),F\$10:F\$700)

and custom format the formula cell as:

[=0]"";0.00

#### texasalynn

##### Well-known Member
one of you sumproducts wasn't anchored on the row, so as you copied down it was changing. Sumproduct gives #N/A when the ranges do not match
Code:
``=IF(SUMPRODUCT((B\$10:B\$700>\$J11)*(B\$10:B\$700<=\$J12)*(F\$10:F\$700))=0,"",SUMPRODUCT((B\$10:\$B\$700>\$J11)*(\$B10:B\$700<=\$J12)*(F\$10:F\$700)))``

Replies
5
Views
152
Replies
4
Views
246
Replies
5
Views
419
Replies
3
Views
65
Replies
3
Views
395

1,136,353
Messages
5,675,283
Members
419,559
Latest member
BraytonM

### 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.

### Which adblocker are you using?

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

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