Wednesday, May 1, 2013

yogi_Compute Row By Row Count Of Specified Strings In Multiple Ranges Spread Over Several Sheets



                                          Google Spreadsheet   Post  #1165
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 01, 2013
user dlgarbett  (http://productforums.google.com/forum/?zx=crpyw0e3sdva#!category-topic/docs/spreadsheets/GNxGPYa_6Vs)
countifs in google spreadsheets
I want to count the number of times a particular string appears in specific rows on my spreadsheets.


see sheet "totals" B119 my formula if it were in excel would be
=countifs(April!C18:G18,April!C37:G37,April!L18:P18,April!L37:P37,May!C18:G18,May!C37:G37,May!C56:G56,May!L18:P18,May!L37:P37,June!C18:G18,June!C37:G37,June!L18:P18,"*SD*")

I have used countif(range,"string")+countif(range,"string")................before for this and I can use it here as well, but I think there must be an easier way.

Please help!

Don
-----------------------------------------------------------------------------------------------------------------
As of now (May-01-2013) Google spreadsheet does not support the use of COUNTIFS functions ... however have a look at the following solution to the problem



Posted by at
Labels: , , ,

2 comments:

  1. DonMay 1, 2013 at 7:52 PM

    Yogi,

    I pasted your formula (which I very excited to see) into my sheet, but it returns "0". I don't know what I'm missing.

    ReplyDelete
  2. Yogi AnandMay 1, 2013 at 7:59 PM

    Hi Don:

    In my blog post, the formula in totals!B119 is

    =ArrayFormula(countif(vmerge( April!C18:G18;April!C37:G37;April!L18:P18;April!L37:P37;May!C18:G18;May!C37:G37; May!C56:G56;May!L18:P18;May!L37:P37; June!C18:G18;June!C37:G37;June!L18:P18);"*"&A119:A123&"*"))

    I should also mentioned that I used VMERGE custom written function (by ahab) available from within the spreadsheet via Tools > Script gallery.

    Let me know if this helps.


    Make It A Great One
    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com




    ReplyDelete

Subscribe to: Post Comments (Atom)