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

congthanh6868

New member
Joined
May 28, 2014
Messages
4
Reaction score
0
Points
0
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
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))
 
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.
 
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
Back
Top