# Thread: VBA formula in stead of Countif & OR/AND to get quick and light file

1. ## VBA formula in stead of Countif & OR/AND to get quick and light file

Hello everyone,

I use below formula Countif, sum, or, and. I look for a VBA to get lighter file. Because the file in fact will be filled up to 40,000 rows.
OR
=IF(F\$1="","",IF(OR(COUNTIF(F\$1:F\$27,\$A30)>0,COUNTIF(F\$1:F\$27,\$B30)>0,COUNTIF(F\$1:F\$27,\$C30)>0,COUNTIF(F\$1:F\$27,\$D30)>0),SUM(COUNTIF(F\$1:F\$27,\$A30),COUNTIF(F\$1:F\$27,\$B30),COUNTIF(F\$1:F\$27,\$C30),COUNTIF(F\$1:F\$27,\$D30)>0),0))
AND
=IF(S\$1="","",IF(AND(COUNTIF(S\$1:S\$27,\$N30)>0,COUNTIF(S\$1:S\$27,\$O30)>0,COUNTIF(S\$1:S\$27,\$P30)>0,COUNTIF(S\$1:S\$27,\$Q30)>0),1,0))

Thank you so much.
Thanh

2. Actually, you have an error in that first formula, it should be

=IF(F\$1="","",IF(OR(COUNTIF(F\$1:F\$27,\$A30)>0,COUNTIF(F\$1:F\$27,\$B30)>0,COUNTIF(F\$1:F\$27,\$C30)>0,COUNTIF(F\$1:F\$27,\$D30)>0),
SUM(COUNTIF(F\$1:F\$27,\$A30),COUNTIF(F\$1:F\$27,\$B30),COUNTIF(F\$1:F\$27,\$C30),COUNTIF(F\$1:F\$27,\$D30)),0))

but it can be simplified to

=IF(F\$1="","",SUMPRODUCT(COUNTIF(F\$1:F\$27,\$A30:\$D30)))

and the second can be reduced to

=IF(S\$1="","",--(SUMPRODUCT(--(COUNTIF(S\$1:S\$27,\$N30:\$Q30)>0))=4))

3. Originally Posted by Bob Phillips
Actually, you have an error in that first formula, it should be

=IF(F\$1="","",IF(OR(COUNTIF(F\$1:F\$27,\$A30)>0,COUNTIF(F\$1:F\$27,\$B30)>0,COUNTIF(F\$1:F\$27,\$C30)>0,COUNTIF(F\$1:F\$27,\$D30)>0),
SUM(COUNTIF(F\$1:F\$27,\$A30),COUNTIF(F\$1:F\$27,\$B30),COUNTIF(F\$1:F\$27,\$C30),COUNTIF(F\$1:F\$27,\$D30)),0))

but it can be simplified to

=IF(F\$1="","",SUMPRODUCT(COUNTIF(F\$1:F\$27,\$A30:\$D30)))

and the second can be reduced to

=IF(S\$1="","",--(SUMPRODUCT(--(COUNTIF(S\$1:S\$27,\$N30:\$Q30)>0))=4))
Thank you for showing my error. Thank you also for two new formula which is shorter. However, it just make the file a little lighter.

If having an other formula to make the file light and quick calculating is better.

Thank you again.

4. Originally Posted by Bob Phillips
Actually, you have an error in that first formula, it should be

=IF(F\$1="","",IF(OR(COUNTIF(F\$1:F\$27,\$A30)>0,COUNTIF(F\$1:F\$27,\$B30)>0,COUNTIF(F\$1:F\$27,\$C30)>0,COUNTIF(F\$1:F\$27,\$D30)>0),
SUM(COUNTIF(F\$1:F\$27,\$A30),COUNTIF(F\$1:F\$27,\$B30),COUNTIF(F\$1:F\$27,\$C30),COUNTIF(F\$1:F\$27,\$D30)),0))

but it can be simplified to

=IF(F\$1="","",SUMPRODUCT(COUNTIF(F\$1:F\$27,\$A30:\$D30)))

and the second can be reduced to

=IF(S\$1="","",--(SUMPRODUCT(--(COUNTIF(S\$1:S\$27,\$N30:\$Q30)>0))=4))
It seems that I was not good at previous explanation. Now I enclose a new file that separate into 2 sheets. You can see the formula will give result when the data updated daily (the formula is filled at all cells of the row).
I expect for a VBA formula to get o light & quick file because with Excel formulas, the file is up to 600Mb, and I have to wait for minutes to get result.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•