Google Spreadsheet Query To Filter Data

Question:

( by Zack18 )

Hello,

I'm performing a "SUMIF" using multiple criteria, so to do this I've created an "IFERROR(SUM(FILTER" function. I want to filter out all cells in a given range that don't contain an "M" or a "D" in the text. This works for one, ie "M" only, but when adding the second, it never works. Here's the equation I've developed:

=iferror(SUM(FILTER(C$3:C$100,B$3:B$100=L31,SEARCH("M",E$3:E$100),SEARCH("D",E3:E10))))

If I were to take out one of those SEARCH functions it would work just fine. Adding the second one creates a problem. Is there any way to make this work?

Thanks for your help,

Zack.

Solution:

Have a look at the following screenshot:

I have the following formula in Cell G1:

=sum(iferror(query(B3:E;"select C where B='"& L31 &"' and ( not E contains 'M' and not E contains 'D') ")))



As you have mentioned in your question:
"I want to filter out all cells in a given range that don't contain an "M" or a "D" in the text."
So I have included not in my query.


You can manipulate the above formula by changing the 'and' to 'or' and removing 'not' as per your requirement.

the above formula takes 'M' as case sensitive, so if you also wish to include search for 'm' and 'd' then add more condition in the query.


NOTE: the above formula will work if you have non-numeric value in Cell L31 and if you have numeric value then try the following formula:

=sum(iferror(query(B3:E;"select C where B="& L31 &" and ( not E contains 'M' and not E contains 'D') ")))

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive