Consulting

Results 1 to 4 of 4

Thread: Is there a cleaner way to write this formula?

  1. #1
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location

    Is there a cleaner way to write this formula?

    =SUM(COUNTIFS($G$4:$O$4,{"Chargeback","pending response","Pending Other","High","Med","X"}))+SUM(COUNTIFS($B$4:$E$4,{"Chargeback","pending response","Pending Other","High","Med","X"}))
    Peace of mind is found in some of the strangest places.

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,872
    If you're certain that column F (or specifically cell F4) can never contain one of those things you're counting then:
    =SUM(COUNTIFS($B$4:$O$4,{"Chargeback","pending response","Pending Other","High","Med","X"}))

    Separately, you could also refer to a named range containing those strings (Chargeback etc.) but you'd have to commit the formula to the sheet using Ctrl+Shift+Enter, not just Enter:
    =SUM(COUNTIFS($G$4:$O$4,Stuff))+SUM(COUNTIFS($B$4:$E$4,Stuff))
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    thanks
    Peace of mind is found in some of the strangest places.

  4. #4
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    Shorter than the original, but less readable IMO:

    =SUMPRODUCT(($B$4:$O$4={"Chargeback";"pending response";"Pending Other";"High";"Med";"X"})*(COLUMN($B$4:$O$4)<>6))
    Be as you wish to seem

Posting Permissions

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