Sum If

[Tikey]

I am using Excel 2016 ( not 365 ) and have a good spec i7 CPU computer.

I have a large spreadsheet containing 250000 rows and growing. Within the sheet one column contains dates in the format 01/01/2018 and another an alpha numerical value in the form AA1234. I am counting the number of unique occurrences of the alpha / number within a certain year. I am currently using the following formula’s to do this, the first for a single year and the second for a range of years.

=SUM(IF(FREQUENCY(IF(YEAR(Records!$F$2:$F$40000)=1992,IF(Records!$T$2:$T$40000<>"",MATCH("~"&Records!$T$2:$T$40000,Records!$T$2:$T$40000&"",0))),ROW(Records!$T$2:$T$40000)-ROW(Records!T$2)+1),1))

=SUM(IF(FREQUENCY(IF(YEAR(Records!$F$2:$F$40000)>=1992,IF(YEAR(Records!$F$2:$F$40000)<=1993,IF(Records!$T$2:$T$40000<>"",MATCH("~"&Records!$T$2:$T$40000,Records!$T$2:$T$40000&"",0)))),ROW(Records!$T$2:$T$40000)-ROW(Records!T$2)+1),1))

Both are entered using CTRL, SHIFT, ENTER.

The problem I am having is that doing this calculation takes several minutes for every occurrence of it and when updating the data and redoing the calcs it has become almost unworkable. You will note in the formula I have restricted it to 40000 rows. Can anyone provide a suggestion as to how to do this more efficiently which will allow Excel to work at a more workable speed, I have thought of a Pivot Table but not sure if this is do able.

Any help appreciated.

 

[Aladin Akyurek]

It returns 0

Good news. Do these fare a bit better?

=SUM(IF(FREQUENCY(IF((Year=1992),MATCH(Alpha,Alpha,0)),Ivec),1))

=SUM(IF(FREQUENCY(IF((Year>=1992)*(Year<=1993),MATCH(Alpha,Alpha,0)),Ivec),1))

 

[Tikey]

Still a long time approx 45secs

 

[Aladin Akyurek]

Still a long time approx 45secs

Wow. You consider 45 secs too long!.. Do you have other formulas with INDIRECT or OFFSET or VLOOKUP in the same workbook?

 

[Tikey]

No other formulas, but I do have a pivot table.

 

[Aladin Akyurek]

No other formulas, but I do have a pivot table.

Well, that should not be a significant factor. So, we are stuck at 45 secs. Other options: Sollicite for a solution using SQL, Power Pivot, etc. or even VBA.

 

[Tikey]

From post 13, do I gather you don't think 45 secs is a long time for this, maybe I am expecting too much ?

 

[Aladin Akyurek]

From post 13, do I gather you don't think 45 secs is a long time for this, maybe I am expecting too much ?

Yeah, I'm pleased with that performance given the sheer amount of data.

 

[Tikey]

Thanks very much for all your help.

 

[Aladin Akyurek]

Thanks very much for all your help.

You are welcome.