Sunday, October 30, 2011

yogi_Use SUMIF With Multiple Criteria

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user garfolino said
SUMIF with multiple criterias
A B
10 Market
15 Guest
32 Payment
41 Bar
20 Dog
43 Visitor
I wanna produce the SUM of everything that is Market, Bar and Dog, meaning that I want to exclude all the other options.
I've been using =SUMIF(B1:B6, "=*market*", A1:A6)+SUMIF(B1:B6, "=*Bar*", A1:A6)+SUMIF(B1:B6, "=*Dog*", A1:A6)
That is too long. So I was wondering if I could use something that says =SUMIF(B1:B6, "=*market*ORdogORbar", A1:A6) <- This is just an example, not a real formula... I did found an option that works for Excel I Created in another Column a RANGE with all the criteria, which is better because I can add or change criterias on the go, and not have to change all the formulas again. =SUMPRODUCT(SUMIF(B1:B6;"*"&$F$2:$F$8&"*";A1:A6)) So from F2 til F8 I have all the criteria including Dog, Market, Bar and others. But this doesnt translate to Google Docs. Any ideia?
-----------------------
in the following I have posted some alternate solutions to the problem



Update:
After I provided the first solution to the user, the user commented
This is where I found the SUMPRODUCT formula.
I just need to translate this into google docs...
http://www.mrexcel.com/forum/showthread.php?t=51693

So I added alternate solutions including the one using the SUMPRODUCT function

7 comments:

  1. Hi Yoga,

    i would like to use this very formula but saying instead as an example: sum if not equal to guest or dog.

    what do you suggest
    Ben

    ReplyDelete
  2. Hi Ben:

    Let me see if I have understood you correctly ...
    I have added a Sheet5 in my blog post where I have added the following alternate formulas:

    formula in cell D2
    =ArrayFormula(sum(if(B:B<>"Guest",if(B:B<>"Dog",A:A))))
    or
    formula in cell D2
    =ArrayFormula(sum(A:A)-sum(sumif(B:B,{"Guest","Dog"},A:A)))
    or
    formula in cell D2
    =ArrayFormula(sum(A:A,sum(sumif(B:B,{"Guest","Dog"},A:A))*{-1}))

    Is this what you are looking for?

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
    Replies
    1. I had a similar problem and your solution works great!

      Thanks Yogi

      Delete
    2. You Are Very Welcome jgcool10 ... Now Let us Keep Googling.


      Make It A Great One
      Cheers!
      Yogi
      Cloud Computing -- Google Docs Way
      yogi--anand-consulting.blogspot.com

      Delete
  3. Hi Ben:

    I have added one more variation of the formula in Sheet5:

    formula in cell D2:

    =ArrayFormula(sum(A:A,sum(sumif(B:B,C:C,A:A))*{-1}))

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  4. I USE THIS TO SUMMARIZE.

    =UNIQUE() to get a unique list of all "DESCRIPTION" texts.

    then SUMIF() to total them.

    This way if you add a few more TYPES in DESCRIPTION, then you will start seeing summary on them too.

    ReplyDelete
  5. Hi Ajay Malhotra:

    Thanks for your comment -- can you tell me, actually show me how you will use the UNIQUE function in solution to the problem addressed herein.

    Cheers!
    Yogi

    ReplyDelete