SUMIF #NAME error

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Leonard of Quirm

Board Regular
Joined
May 19, 2005
Messages
180
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

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
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

Board Regular
Joined
May 19, 2005
Messages
180
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

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
Noted, I have reduced the ranges to 300 rows.

Cheers
Colin.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,706
Members
412,481
Latest member
nhantam
Top