Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SumIf and Sum Wildcards (XP: SR2)

    I can successfully pass a wildcard of " * " in the formula "SUMIF" but now "SUM".

    Does anyone have an idea as to why it behaves this way?

    Thanks,
    John

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

    Re: SumIf and Sum Wildcards (XP: SR2)

    Because SUMIF (like COUNTIF) has a separate Criteria argument that was designed to be flexible: you can include > and * etc.
    SUM does not have a separate Criteria argument. You could use

    =SUM((LEFT(A5:A9,2)="60")*(B5:B9="Dollars")*C5:C9)

    Don't forget to confirm with Ctrl+Shift+Enter, since this is an array formula.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIf and Sum Wildcards (XP: SR2)

    Hans,

    Thanks for the quick response. It now makes sense.

    John

  4. #4
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIf and Sum Wildcards (XP: SR2)

    A non array formula, although slightly longer than Han's,

    =SUMPRODUCT((ISNUMBER(SEARCH("60",A5:A9)))*(B5:B9= "dollars")*(C5:C9))

Posting Permissions

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