SUMIF #NAME error

[rs2k]

This formula is returning a '#NAME' error.

=SUMIF(A5:A,L5,D5:D)

When I tried it out on a new sheet it worked OK, but on the WS I need it on it produces the error, the ranges A5:A,L5,D5:D are populated by some code, all the cells are formatted as general.

Can anyone help/explain whats going wrong?

Cheers
Colin.

 

[Leonard of Quirm]

Colin,
That's a strange notation your using A5:A or D5:D.
What's the limit of your range?

Do you mean A5:A65536 & D5:D65536 or just plain A:A and D:D?

 

[rs2k]

Leonard,
Thank you for that, A5:A65536 & D5:D65536 did the job.
btw, my range changes and I thought (wrongly) that A5:A would capture all the data from A5 down.

Thanks again,
Colin.

 

[Leonard of Quirm]

No worries.

A word of warning though: using large sumif ranges like that can slow down your workbook immensely. It's not a problem if you have only one or two such formulae in your workbook, but if you have many sumifs, you should endeavour to make your ranges as specific (as small) as possible.

 

[rs2k]

Noted, I have reduced the ranges to 300 rows.

Cheers
Colin.