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

#### congthanh6868

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

#### Attachments

• Cong thuc thay the nhom Countif ket hop OR hoac AND.xls
24.5 KB · Views: 14

#### Bob Phillips

##### Super Moderator
Staff member
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))

#### congthanh6868

##### New member
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.

#### congthanh6868

##### New member
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.

#### Attachments

• VBA formula to replace Countif INTL.xls
35.5 KB · Views: 15