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