Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Formula (2003)

    Hi,

    I have a very complex worksheet with the a variation of formula's that are used thousands of times over.

    Would it be possible to simplify the following three formulas into one?

    In Q104 =IF(P104="","",COUNTIF(X_DR_1,P104)) - How many debits do I have identical to P104
    In R104 =IF(P104="","",COUNTIF(X_CR_1,P104)) - How many credits do I have identical to P104
    In S104 =IF(P104="","",IF(R104/Q104=1,"MATCH","NO MATCH")) - Do I have the same number of debits and credits

    Any suggestions greatfully received!!

    Thanks

    Nath

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Formula (2003)

    Assuming you're only interested in whether they match:
    =IF(P104="","",IF(COUNTIF(X_CR_1,P104)=COUNTIF(X_D R_1,P104),"MATCH","NO MATCH"))
    should do it.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (2003)

    Hans, I cannot thank you enough!!! That should speed the worksheet up a lot!!!

    Many Thanks
    Nath

    P.S, nearly brakfast time, diamond shreddies here I come..... :-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Formula (2003)

    Er, it was Rory who answered your question!

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (2003)

    LOL, sorry, am in sleep mode!!

    Cheers Rory!!!!

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Formula (2003)

    Glad to help.
    Regards,
    Hans. Er, no wait, Rory. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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