Results 1 to 4 of 4

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

  1. #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

  2. #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. #3
    Quote Originally Posted by Bob Phillips View Post
    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. #4
    Quote Originally Posted by Bob Phillips View Post
    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.
    Attached Files Attached Files

Posting Permissions

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