# 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

Register for a FREE account, and/
or Log in to avoid these ads!

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))

Please anyone can help me.

Thank you so much.
Thanh  Reply With Quote

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))  Reply With Quote

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.  Reply With Quote

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))
Thank you for your help.
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.  Reply With Quote

#### Posting Permissions

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