Summing values in one column if criteria met in another, quickly.

[geoffreygroh]

I'm trying to sum cells in column B, if their value in column A is the same. Unfortunately the values in column A will be different everyday. The way I do it currently is to use the advance filter to copy unique values of column A to column C. After I know the unique values in Column C I run an Array function =IF(ISBLANK($C2),"",SUM(IF(($A:$A=$C2),B:B,0)))
and I fill the column of cells with the formula.

The macro I use is basically just an edited "record macro" code. and it take quite a long time to calculate all the array formulas.

I was hoping someone knows a better/faster way to write this in order to decrease calculation time….

 

[Trevor G]

Welcome to the forum,

With the macro you have recorded you can switch the screen updating, warnings and autocalculate off until it finishes running this would speed things up.

Maybe showing your code in your thread would help as well.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> cl()<br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>Application.Calculation = xlCalculationManual<br><SPAN style="color:#007F00">'Your code goes here</SPAN><br><br>Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br>Application.Calculation = xlCalculationAutomatic<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[SteveO59L]

Try a pivot table

 

[nicolai185]

I have an idea that may or may not be what you're looking for. You could use a simple if statement to determine if the value in column A is equal to the value in column B and return that value in column C.

In C2, type if(A2=B2,B2). It will only return a 0 if false, so it won't affect your sum. Then in column D, just do a SUM(C:C) and it will return the total value.

Hopefully something that might work for you.

 

[Dryver14]

Do you need to sum all of column A and B if you can reduce that to say 500 you will save time

 

[geoffreygroh]

The example I used is kind of the bare bones of what I'm doing. In actuality I need to do this procedure across tens of thousands of rows, and return the sums of multiple columns instead of just one. So I'm basically just repeating the bare bones procedure a lot.... So significant speed up is very import.

Trevor,
Thanks for the idea but it doesn't quicken it up as much as I hope.

Steve,
I've never used one, but I'll do some research.

Nicolia,
I think understand what you're getting at, but I don't think it will be practical, I want to sum the values in Column B if their value in A is the same, returning a list that will be much shorter.

Dryver,
I need to check a lot of cells, do you think That A2:A100000 would be a lot quicker than A:A?

I was hoping there was a way to store values in a VBA or something so that the calculation would speed...

Thanks for everyone ideas.


​

 

[Dryver14]

I'd say yes as there are a million rows