Row limitation to excel sumifs
Hi,
I am an amateur and self-taught excel user.
My needs are easily met by excel formulae of sum, sumproduct, sumif and sumifs.
However, in my latest worksheet (which will eventually run into a million rows) I have found that my sumifs formula does not work beyond row 55000.
Help!!!
=SUMIFS(Catalogue!$E$3:$E$55000,Catalogue!$C$3:$C$55000,GrdnAvg!A19,Catalogue!$K$3:$K$55000,"Leaf")
Surajit Phukan
Assam, India
Have you tried
=SUMIFS(Catalogue!$E:$E,Catalogue!$C:$C,GrdnAvg!A19,Catalogue!$K:$K,"Leaf")
Hey this seems to be working for my present set of data which has 23652 rows.
Will it work beyond 55000 rows is what I am wondering?
Any way of finding out before my data crosses the 55000 row mark?
Thanks in anticipation.
Put some data that matches in row 55001.
You do realize that, if the formula that didn't work is the same as the one in your original post, the ranges in the formula are what's limiting it to row 55000. If you had changed the formula to:
=SUMIFS(Catalogue!$E$3:$E$65000,Catalogue!$C$3:$C$65000,GrdnAvg!A19,Catalogue!$K$3:$K$65000,"Leaf")
you'd be good for another 10,000 rows
No, I tried using =SUMIFS(Catalogue!$E$3:$E$65000,Catalogue!$C$3:$C$65000,GrdnAvg!A19,Catalogue!$K$3:$K$65000,"Leaf")
But got an error report. And then I read somewhere that there is a row limitation to excel.
Is there a row limitation to excel 2007?
The row limit in 2007 is 1,048,576 but it must be 2007 file type - like .xlsx, xlsm
The row limit in previous versions is 65,536.
If you're using an xls type file then save it as the newer version to get the added rows.
Hey thanks! That is the silly mistake I was making. My file was an excel 97 format.
One last question:
I had by mistake keyed in:
=SUMIFS(Catalogue!E:E,Catalogue!C:C,GrdnAvg!A19,Catalogue!K:K,"Leaf")
instead of:
=SUMIFS(Catalogue!$E:$E,Catalogue!$C:$C,GrdnAvg!A19,Catalogue!$K:$K,"Leaf")
And it worked. What is the difference between the two?
You would only need the $'s to 'lock' the column references in order to maintain them when copying to a new column.
It's called Absolute vs Relative referencing and it's VERY important for you to learn how to use it if you want to be good at working with formulas.
Here's a link for you to read about it: http://www.cpearson.com/excel/relative.aspx
Last edited by Cutter; 06-16-2012 at 01:56 PM.
Many many thanks Cutter. You have made my day
Cheers! Now off to watch Euro Cup 2012 ...
You're welcome, but before you go, don't forget to mark your thread as SOLVED (click on Forum Rules @ top of page and see instructions in rule #9).
Enjoy the game(s).
And thanks for the 'star tap'.
Last edited by Cutter; 06-16-2012 at 02:44 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks