yogi_ Count Number Of Items By Type From Multiple Items In Each Type

                                         Google Spreadsheet   Post  #1644
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-20-2014
post by Samford: (https://productforums.google.com/forum/#!mydiscussions/docs/6k2s4kRVz4g)
Counting multiple criteria - formula works in old version of sheets but not the new!

Can anyone explain why this formula:

=sum(countif(C8:C12,{"DCP","CO2","WC","F","FM"}))

... works perfectly in the old version of Sheets and counts all the criteria listed, but when applied in the new Sheets - it doesn't work properly.

Here's how it looks in the new version of Sheets:

=sum(countif(C12:C25,{"DCP","CO2","WC","F","FM"})) This formula in the new Sheets only returns the number of items listed as "DCP" which is only the first criteria listed.

What has changed???

Any help is most appreciated!

---

Hi Yogi

thank you so very much for looking at this problem for me.  I tried your suggestion and it returned a value of "0" - which is not correct.

Here is a link to the spreadsheet, if that helps:

https://docs.google.com/spreadsheets/d/1Gpmry5OuwM-oHGS4Mor_U4s3M-MZ7EkIz9_a3V-jeJI/edit?usp=sharing

Thanking you in advance..

--------------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Pull Data Using QUERY Function Where Column B In 'total data' Contains Specified Word regardless Of Its Case (case insensitive query)

                                         Google Spreadsheet   Post  #1602
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-14-2014
post by Samford: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/ITxQEceqe5E)
Is it possible to convert all text within a sheet to all lowercase or all uppercase?

I would like to use this formula to extract data from a master sheet:

=query('total data'!A3:D,"Select * Where B contains """&B2&""" ")

It works really well, but I am finding the "contains" option will only return matching case text from the B column. I figure if I can format all the text in the 'total data' B column to be either upper or lower case, then the problem is solved!

I'd be most grateful for any solutions or other suggestions.

---------------------------------------------------------------------------------------------------------------------------