Google Spreadsheet Advanced Query Solution

Question:

( by Dan Moyer )

Thanks in advance for any help you may be able to provide.

I am trying to write a formula that will search for a word (e.g. salem) within the contents of any cell from C7 to C300.  For every cell in which it finds the word, I would like the formula to include that number when averaging all cells between H7 and H300 that have a corresponding cell in the C7 to C300 range that includes the word salem?  Does this make sense?  Is this possible?  Thank you!

=AVERAGE(IF(C7:C300="salem",H7:H300,False))

======================

My Reply:

If you want to search for the exact phrase then try this:
=average(iferror(filter(H7:H300;C7:C300="Kishan")))

If you want to search for the contents of each cell that contains phrase then try this:
=average(iferror(query(C7:H300;"select H where C contains 'Kishan' ")))

And you can also try this:

=sumif(C7:C300;"*Kishan*";H7:H300) / countif(C7:C300;"*Kishan*")

======================

Dan:

Kishan, this is terrific.  Thank you!  One more question though...this formula doesn't seem to take into account empty cells...

So, for example, if Kishan appears ten times but there is only one value in H7:H300 corresponding to a reference to Kishan, it still divides by ten?  Is there a way to have it ignore a "Kishan" cell in the count of total "Kishan" cells when the corresponding H7:H300 cell is blank?  Thank you for your assistance.

======================

My Reply:

In this case it would be better if you share your spreadsheet with sample but realistic data.

======================

Dan:

Kishan, thanks for the assistance.  I posted the spreadsheet as requested.  Please let me know if you are able to offer further assistance.  Once again, thank you!

======================

My Reply:

Instead of:

=iferror(average(query(C11:H300;"select H where C contains 'abiding' ")))

try  this:

=iferror(average(query(C11:H300;"select H where C contains 'Abiding' ")))

You must be having problem because of case sensitive issue...

======================

Solution:

Now, instead of having formulas in each and every cell, you can optimize the performance of your spreadsheet by using ArrayFormulas and by reducing these excessive formulas.
 Have a look at the following screenshot of my spreadsheet:

I have the following formula in Cell B2 of "HSPT School Results":

=iferror(arrayformula(round(query(transpose((query(arrayformula(if({1,0,0,0,0,0,0,0,0,0,0,0,0};"A";A11:M300));"select Col1,avg(Col8),avg(Col9),avg(Col10),avg(Col11),avg(Col12),avg(Col13) where lower(Col3) contains 'abiding' group by Col1")));"select Col2 offset 1";0);2)))

the above formula will give you the results for Range "B2:B7"


Similarly put the following formula in Cell F2:

=iferror(arrayformula(round(query(transpose((query(arrayformula(if({1,0,0,0,0,0,0,0,0,0,0,0,0};"A";A11:M300));"select Col1,avg(Col8),avg(Col9),avg(Col10),avg(Col11),avg(Col12),avg(Col13) where lower(Col3) contains 'bethany' group by Col1")));"select Col2 offset 1";0);2)))


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