Question on SUMPRODUCT

[Pauljj]

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
	J	K	L	M
10	25/06/2006	1288.71
11	02/07/2006	#N/A
12	09/07/2006	#N/A
13	16/07/2006	#N/A
Data

Can anyone see why this would happen?

 

[Aladin Akyurek]

$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]

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

=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)))