sumproduct sumifs multiple criteria
Results 1 to 5 of 5

Thread: sumproduct sumifs multiple criteria
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2011
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default sumproduct sumifs multiple criteria

    I currently have a sumproduct sumif formula pulling wildcards and want to change it to sumifs where it only includes values <0. Here is my current formula:

    =SUMPRODUCT(SUMIF($C:$C,"*"&{"ACORNS"}&"*",$D:$D))

    i tried changing it to this, but it returned 0, when there are definitely values for it:
    =SUMPRODUCT(SUMIFS($D:$D,$C:$C,"*"&{"ACORNS"}&"*",$C:$C,"<0"))

    any ideas? thanks in advance!

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,046
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: sumproduct sumifs multiple criteria

    Why do you need the braces?
    This should work

    =SUMPRODUCT(SUMIF($C:$C,"*ACORNS*",$D:$D))
    Last edited by Special-K99; Jun 24th, 2019 at 11:01 AM.

  3. #3
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    300
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sumproduct sumifs multiple criteria

    Hi,

    Does the values in C Column are all numbers ? You can use =ISNUMBER(C1) and copy it down and check that all the values should be TRUE.

    I tried replicating your case it seemed to work for me:

    B C D
    1 -1 A -6
    2 0 A
    3 -2 Ab
    4 -3 B
    5 -2 B
    6 1 A
    7 2 B
    8 -3 Ac
    9 3 B
    Sheet1

    Worksheet Formulas
    Cell Formula
    D1 =SUMIFS(B1:B9,C1:C9,"A"&"*",B1:B9,"<0")

  4. #4
    New Member
    Join Date
    Aug 2011
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sumproduct sumifs multiple criteria

    Quote Originally Posted by Special-K99 View Post
    Why do you need the braces?
    This should work

    =SUMPRODUCT(SUMIF($C:$C,"*ACORNS*",$D:$D))
    hmm, I'm not sure...I've been using this formula forever. Maybe an older version of excel needed it?

  5. #5
    New Member
    Join Date
    Aug 2011
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sumproduct sumifs multiple criteria

    Quote Originally Posted by Aryatect View Post
    Hi,

    Does the values in C Column are all numbers ? You can use =ISNUMBER(C1) and copy it down and check that all the values should be TRUE.

    I tried replicating your case it seemed to work for me:

    B C D
    1 -1 A -6
    2 0 A
    3 -2 Ab
    4 -3 B
    5 -2 B
    6 1 A
    7 2 B
    8 -3 Ac
    9 3 B
    Sheet1

    Worksheet Formulas
    Cell Formula
    D1 =SUMIFS(B1:B9,C1:C9,"A"&"*",B1:B9,"<0")
    Thanks! With a couple modifications, this worked as well.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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