SUMIFS stops after 400 rows

[roberto188]

I have a sumifs function and it works perfectly up until row 400 then it stops calculating and just ignores all the data below row 400. If I specify my sumifs to only do the first 400 rows then add that to another sumifs that does rows 401 to X I get the correct value. Is there some kind of limitation on the number of rows sumifs can use? This is really annoying because I have over 20,000 rows of data that I need to apply this function to. Thanks!

[Norie]

Any chance you could upload a sample workbook?

Click on GO ADVANCED, scroll down and click Manage Attachments.

[Tony Valko]

Is there some kind of limitation on the number of rows sumifs can use?

No limit.

Sounds kind of odd that if you use a separate SUMIFS from row 401+ that you get the correct result.

Can you post both formulas?

[roberto188]

Here is the formula:

=IF(A7=0,"",ROUND(SUMIFS(Transcations!$C:$C,Transcations!$D:$D,A7,Transcations!$B:$B,"Bought")+SUMIFS(Transcations!$C:$C,Transcations!$D:$D,A7,Transcations!$C:$C,"<0")+SUMIFS(Transcations!$C:$C,Transcations!$D:$D,A7,Transcations!$B:$B,"Bookkeeping"),2))

This is the formula that stops after row 400.

[63falcondude]

Here is the formula:

=IF(A7=0,"",ROUND(SUMIFS(Transcations!$C:$C,Transcations!$D:$D,A7,Transcations!$B:$B,"Bought")+SUMIFS(Transcations!$C:$C,Transcations!$D:$D,A7,Transcations!$C:$C,"<0")+SUMIFS(Transcations!$C:$C,Transcations!$D:$D,A7,Transcations!$B:$B,"Bookkeeping"),2))

This is the formula that stops after row 400.

What is in cell A401?

[roberto188]

Figured it out. For whatever reason the word "bookkeeping" was not be recongnized. In fact none of my text that was put into the sheet was recognized. When i RETYPE over the old "bookkeeping" or "bought" it would work. This is problem because I'm copying and pasting tons of data. I cannot manually input this data. Any ideas what could be causing this?

[63falcondude]

Figured it out. For whatever reason the word "bookkeeping" was not be recongnized. In fact none of my text that was put into the sheet was recognized. When i RETYPE over the old "bookkeeping" or "bought" it would work. This is problem because I'm copying and pasting tons of data. I cannot manually input this data. Any ideas what could be causing this?

Try using text to columns, Finish on column B. Does that solve the issue?

[roberto188]

I'm not sure what you mean by TEXT to columns. I'm not importing text. It's excel data that I am copying and pasting into the sheet. It's strange now, the overwrite with bookkeeping works, but not "bought".

[63falcondude]

I'm not sure what you mean by TEXT to columns. I'm not importing text. It's excel data that I am copying and pasting into the sheet. It's strange now, the overwrite with bookkeeping works, but not "bought".

Highlight column B, go to the Data tab, select Text to Columns, Finish. See if this changes anything.

[roberto188]

Highlight column B, go to the Data tab, select Text to Columns, Finish. See if this changes anything.

Didn't help. I'll just post my spreadhseet and you guys can take a look. It's acting very funny.

[roberto188]

I did some more experimenting and again, when I manually input the search criteria in the Transcations tab, "Bought, Sold, Bookkeeping" the formula works. But when it's copied from another spreadsheet and pasted it doesn't. Even doing a find and replace doesn't seem to help.

[roberto188]

It's definitely something wrong with my data that I am importing. Manual inputs work perfectly fine. I just can't understand what the issue could possibly be.

[MrShorty]

It took a minute, but the problem appears to be that some cases of "Bookkeeping" have a trailing space and some do not. I tested by putting =LEN(B2) into a spare column on Transactions and copied down. Then compared successive values of this function. I noticed that some cases of Bookkeeping" had 11 characters and others had 12. Investigating those that had 12 showed that they were carrying an extra space "Bookkeeping ". To Excel, the text string "Bookkeeping" is not the same as the text string "Bookkeeping ".

Solution is to somehow figure out how to copy data into Transactions without carrying any leading or trailing spaces, or to modify the conditional sum (maybe using wildcards) so that leading or trailing spaces do not interfere.

[roberto188]

You are the MAN! A simple find and replace "Bookkeeping_" with "Bookkeeping" did the trick. Thanks so much!