VBA version of this front-of-house formula to make workbook less heavy

[RoseG]

Hey all,

Could anyone possibly advise if there is some VBA version of the following formula, that I could use. My workbook is all slow and sluggish because this thing is so bluberous.

=IF(AND(B4<>"",(SUMPRODUCT(--($B$4:B4=B4),(--($D$4:D4=D4)),(--($S$4:S4=S4))))<=1,(SUMPRODUCT(--(B4:$B$2000=B4),(--(D4:$D$2000=D4)),(--(S4:$S$2000=S4))))>1),SUMPRODUCT((B4:$B$2000=B4)*(D4:$D$2000=D4)*(S4:$S$2000=S4),(AF4:$AF$2000)),"")

Key information:
Column B is a date
Column D is a name
Column S is a location
Column AF is an ammount of time

I want to count up the ammount of time that the same person went to the same place on the same day and enter this info in column AN, on the first row of this set.

What I want Excel to do is:

Look in Columns B, D and S to see if there are duplicated entries e.g.
B1=14/04/2011, D1=John Wayne and S1=South Library
B2=15/04/2011, D2=John Wayne and S2=South Library
B3=14/04/2011, D3=John Wayne and S3=South Library

Row 1 and 3 are a match. No other matches.

Then I want it to add up the total ammount of time from column AF and enter this in column AN on the first row of the matching rows.
In the example given, this would be row 1. No entry to appear in rows 2 or 3.

Oh and I don't want Excel giving me any of its lip if no info has been entered yet. Hence the condition of B<>"" in the formula.

Is there anyone who can help?

Rose xxxx

 

[RoryA]

You would almost certainly be better off adding a new key column that concatenates columns B, D and S, and you can then use SUMIF/COUNTIF which will be a lot more efficient.

 

[RoseG]

Top banana!

No more sluggish worksheet with this new improved fromula:

=IF(AND(COUNTIF(AN13:$AN$2000,AN13)>1,COUNTIF($AN$4:AN13,AN13)=1),SUMIF(AN13:$AN$2000,AN13,AF13:$AF$2000),"")

Might even slim it down with a SUMPRODUCT if I can figure out how.

Thanks so much!

 

[RoseG]

Hey all,

Sorry to bug with an old issue but, now I've filled in the rest of my sheet, the Concatenate solution is still slowing the book down something chronic.

Any further suggestions? I'd really appreciate it.

Thanks muchly!