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
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
Hi Yoga,
ReplyDeletei 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
Hi Ben:
ReplyDeleteLet 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
I had a similar problem and your solution works great!
DeleteThanks Yogi
You Are Very Welcome jgcool10 ... Now Let us Keep Googling.
DeleteMake It A Great One
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
Hi Ben:
ReplyDeleteI 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
I USE THIS TO SUMMARIZE.
ReplyDelete=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.
Hi Ajay Malhotra:
ReplyDeleteThanks 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