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

#### RoseG

##### New Member
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

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### RoryA

##### MrExcel MVP, Moderator
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

##### New Member
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

##### New Member
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!

Replies
4
Views
84
Replies
0
Views
25
Replies
10
Views
119
Replies
1
Views
108
Replies
3
Views
441

1,109,444
Messages
5,528,799
Members
409,835
Latest member
Mafu1267

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...