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]

In G2 of Records enter and copy down:

=YEAR(F2)

1. Define Lrow in Formulas | Name Manager as referring to

=MATCH(9.99999999999999E+307,Records!$F:$F)

2. Define Date in Formulas | Name Manager as referring to

=Records!$F$2:INDEX(Records!$F:$F,Lrow)

3. Define Year in Formulas | Name Manager as referring to

=Records!$G$2:INDEX(Records!$G:$G,Lrow)

4. Define Alpha in Formulas | Name Manager as referring to

=Records!$T$2:INDEX(Records!$T:$T,Lrow)

5. Define Ivec in Formulas | Name Manager as referring to

=ROW(Date)-ROW(INDEX(Date,1,1))+1

Now invoke, control+shift+enter:

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

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

Make these the performance bearable?








Define Ivec thru Formulas | Name Manager as referring to

 

[Tikey]

Thanks for your reply. Before I try it can i ask why in 3. you have used G, is this a typo should it be F ?

 

[Tikey]

Apologies, I hadn't read the first line of your reply which deals with column G.

I get an error for the first formula, saying there is a problem with it, not sure but I think a bracket is missing. The second works fine, but unfortunately still takes a long time.

 

[Aladin Akyurek]

Apologies, I hadn't read the first line of your reply which deals with column G.

I get an error for the first formula, saying there is a problem with it, not sure but I think a bracket is missing. The second works fine, but unfortunately still takes a long time.

This...

=YEAR(F2)

formula?

 

[Tikey]

I have found the problem with formula 1. There was a bracket missing before YEAR. This now works but as in my previous post it still takes a long time, do you think there is a way around this ?

 

[Aladin Akyurek]

I have found the problem with formula 1. There was a bracket missing before YEAR. This now works but as in my previous post it still takes a long time, do you think there is a way around this ?

Yes, I see. We are missing an opening paren (bracket): =SUM(IF(FREQUENCY(IF((Year=1992)*(Alpha<>""),MATCH(Alpha,Alpha,0)),Ivec),1))

What does =COUNTIFS(Alpha,"") return?

 

[Tikey]

Sorry, I'm not sure where I should insert =COUNTIFS(Alpha,"")

 

[Aladin Akyurek]

Sorry, I'm not sure where I should insert =COUNTIFS(Alpha,"")

Somewhere outside the data area. We are trying to assess whether any blanks/empty cells in the range Alpha stands for.

 

[Tikey]

It returns 0