# VBA to enter 50,000 SUMIF's faster?

1. ## VBA to enter 50,000 SUMIF's faster?

I have a simple report I do every week, it only needs one formula;

=SUMIF(A:A,A2,B:B)

Which is fine, a nice simple formula. Takes no time at all to enter....until I copy it down 50,000+ rows, and it takes about 3-4 minutes to calculate it all making my computer a laggy space heater while it calculates...

Entering absolute references instead of entire column references, SUMIF(\$A\$2:\$A\$50000,A2,\$B\$2:\$B\$50000), does not speed it up in any noticeable way (actually it seemed a little slower)
.
Can this be done (significantly) faster with VBA, or a different method I haven't thought of? A 3.24% faster method is of no use to me, I'm looking for 50%+!

If you need a sample just
=RANDBETWEEN(10,99) in A1:A50000
and
=RANDBETWEEN(50,500000) in B1:B50000
Then in C1
=SUMIF(A:A,A1,B:B)
And copy to the end

that goes about twice as fast as my actual data, but it still crawls along pretty slowly.

A warning on calculation speed, I don't know how much these details play into calculation speeds but if you are running on significantly lower hardware (like a Pentium M or something), it may take a really long time to calculate and excel might lock up, close any important spreadsheets just in case! I am on a Core i5 3320M with 8GB Ram and have Office Professional Plus 2010 64 bit.

Thanks!

2. ## Re: VBA to enter 50,000 SUMIF's faster?

Pivot table, in the blink of an eye.

3. ## Re: VBA to enter 50,000 SUMIF's faster?

SUMIF is an array formula, it's doing individual math on every used row in your range. So if there are 50,000 rows of data, that's 50k calcs PER CELL. In small doses, that's fine

But even with vba, it still takes a long time to do 50k cells with 50k calcs in each cell (2,500,000,000 calcs)

Consider using a Pivot Table instead to give you the SUM of each unique value in your table, should be significantly faster for you.

4. ## Re: VBA to enter 50,000 SUMIF's faster?

Unfortunately that won't work. There are a total of 4 columns, and it needs to remain in a upload-able format for our system, I don't think the system would be able to digest a pivot table. Maybe I can pivot just the two columns and do a VLOOKUP, but I don't know if that would be faster.

I'm going home so I won't be able to reply on this until tomorrow morning, appreciate the reply and I will check again tomorrow!

5. ## Re: VBA to enter 50,000 SUMIF's faster?

What would be regarded as an uploadable format?

I can create a pivot table with your sample formulas copied down colums A and B for 50000 rows in a split second.

The pivot table simply consists of two columns, one for the ID (column A) and one for the sum of column B for each ID.

There is a grand total row but that can easily be removed.

6. ## Re: VBA to enter 50,000 SUMIF's faster?

Originally Posted by Speshul
Maybe I can pivot just the two columns and do a VLOOKUP, but I don't know if that would be faster.
It would be infinitely faster.

7. ## Re: VBA to enter 50,000 SUMIF's faster?

if your formulas are in column e maybe sort on column a then use this in e2
=if(a2=a1,e1,if(a2=a3,SUMIF(A:A,A2,B:B),b2))
and fill down

8. ## Re: VBA to enter 50,000 SUMIF's faster?

Great suggestion, Joseph.

9. ## Re: VBA to enter 50,000 SUMIF's faster?

@Speshul

How fast depends on whether you want the result to show the sumif formulas in each cell or just their resulting values.

Entering formulas is, as you have found out, likely to be quite slow with a lot of rows. Although I do think the times you give could be speeded up somewhat (needs a bit of a jack-up) if you still require the formulas in each cell.

If you want the entries in (say) Col E to be the values, VBA can do it a lot faster.

On your test data suggestion (values of Randbetweens down to row 50,000) try this code and check how fast
``Please Login or Register  to view this content.``

10. ## Re: VBA to enter 50,000 SUMIF's faster?

Originally Posted by JosephP
if your formulas are in column e maybe sort on column a then use this in e2
=if(a2=a1,e1,if(a2=a3,SUMIF(A:A,A2,B:B),b2))
and fill down
This solution works great for me. It is significantly faster (calculation 9% per second, instead of 1% per 4 seconds). I had not thought of making the sumif a condition, that significantly reduces the number of calculations the sheet has to calculate, makes perfect sense

kalak, you are correct in your assumption that I do not need the formulas, only the result. Unfortunately your VBA gives me a type mismatch error on line 12
u(i, 1) = d(a(i, 1))
VBA arrays are still a magical mystery to me so I am not sure why the error is occurring. However the formula mentioned by JosephP will work fine.

Thanks everyone for the help.

11. ## Re: VBA to enter 50,000 SUMIF's faster?

@ Speshul,

Good that you now have an adequate method and thanks for the feedback.

Just to tidy up.
The type mismatch error occurs if there are non-numeric values in Col B. i.e they can't be added.
The most likely cause is non-numeric column headers. I only tested the code on your suggested test data in your post#1, which was non-numeric all the way down, without non-numeric headers. Hence no error.

It took my average spec laptop about a quarter of a second to give the same (value) result as the formula approach for your 50000 rows test data.
Allowing for non-numeric data is actually very easy. In red below
``Please Login or Register  to view this content.``
Incidentally, you can likely make the formula approach somewhat faster by having it consider only the used parts of columns A and B, rather than the entire columns.
can make change manually or through VBA.

12. ## Re: VBA to enter 50,000 SUMIF's faster?

Just a quick thought.

Since you are sorting it anyway - it should be even faster if you calculate the maximum number of repeats in column A using mode and match, and set the sumif to only look at that many cells down from the current row (to avoid it having to look at the whole range).

13. ## Re: VBA to enter 50,000 SUMIF's faster?

I still think the given solution in #5 of Norie (pivot table) did not get the attention it deserved.

14. ## Re: VBA to enter 50,000 SUMIF's faster?

Speshul

You really should consider a pivot table.

If you have a problem with the format you could always copy/paste special.

15. ## Re: VBA to enter 50,000 SUMIF's faster?

pivot table can be somewhat slow in larger datasets. if large enough sometimes doesn't seem to work at all
OP was concerned (it seems) primarily about slowness
So just run a fast(er) macro
with OP's suggested test data in post#1
If you need a sample just
=RANDBETWEEN(10,99) in A1:A50000
and
=RANDBETWEEN(50,500000) in B1:B50000
code below does, on my average spec laptop, the equivalent SUMIF calculations in well under 0.1 seconds.
``Please Login or Register  to view this content.``
How long would a pivot table take? (From evidence, not conjecture.)

16. ## Re: VBA to enter 50,000 SUMIF's faster?

I have no idea of how long it might take with the dataset/setup the OP actually has because I know very little about that dataset/setup.

With the formulas the OP suggested in post #1, extended down to approx. 100,000 rows, a pivot table took a few seconds.

Mind you if I had that amount of data I'd probably be thinking of moving to a database where a simple query could produce the required result.

17. ## Re: VBA to enter 50,000 SUMIF's faster?

Yuor code: n = Cells(Rows.Count, 1).End(3).Row
finds the last used cell in column 1 (A) - great. But what is the End(3) about - I can see it woks - but why?

18. ## Re: VBA to enter 50,000 SUMIF's faster?

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1