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

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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
Joined
May 28, 2014
Messages
4
Reaction score
0
Points
0
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
Joined
May 28, 2014
Messages
4
Reaction score
0
Points
0
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
Top