Monday, April 9, 2012

yogi_Workaround For Countifs Function In Google Spreadsheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #500    Apr 9, 2012     www.energyefficientbuild.com

user JimP. said: 
Excel -> Docs "Countifs with Wildcards" Spreadsheet Dilemma 
 Banging my head against the CountIFs wall, and wondering if someone could pop in and help. Example Sheet here: https://docs.google.com/spreadsheet/ccc?key=0AsjxApV_UI9GdHZ5WC00NThzcW5FSFRRSTdxRHdYZGc I'm doing an inventory where I need to count the number of URLs that contain a specific URL fragment in Group X. 
In Excel, this was accomplished with: =COUNTIFS(B:B,"http://www.mywebsite.com/events/*",A:A,"Group 1") 
Count the number of URLs that contain "http://www.mywebsite.com/events/" that are also in "Group 1". 
I tried numerous versions of CountA, but I think the wildcard is screwing it up and making it always show "0" as a result. Can someone help with the right way to convert this? If done right, the correct answer is 5. Bonus Points if you can provide a formula that I can use in place of CountIfs in Excel that would accomplish this same task, because I would rather have it imported right than re-edit the problem cells every time. Thanks in advance, 
JimP.
-----------------------------------------------------
following is a solution to the problem
 

13 comments:

  1. Thank you,
    The information you shared is very informative
    Here’s what a business leader must know.
    Power of Cloud Computing

    ReplyDelete
  2. I am glad you found the information in this blog post to be very informative ... Now Let Us Keep Googling.

    Cheers!
    Yogi

    ReplyDelete
  3. Hi, I have a similar problem trying to get a Countifs calculation in Excel to work in Google Docs and I can't get it to work. Here's the Excel calculation and then I'll explain:


    =COUNTIFS('DC Sales Leads'!$E$2:$E$999,"<"&E$2,'DC Sales Leads'!$A$2:$A$999,$B5)-COUNTIFS('DC Sales Leads'!$E$2:$E$999,"<"&D$2,'DC Sales Leads'!$A$2:$A$999,$B5)


    The "DC Sales Leads" tab contains sales people (column A), dates (Column E), and the number of leads that have come in. I am trying to capture the data on a monthly basis by sales person.

    In the above calculation E$2 is February 2012 and D$2 is January 2012, and $B5 refers specifically to sales person, John.

    I think that I need to translate the above Excel calculation into an Array Formula to get it to work in Google Docs but I can't figure it out.

    Can you help?

    ReplyDelete
  4. Sorry, one clarification on the above post...

    The "DC Sales Leads" tab contains SALES PEOPLE (column A) and the DATES that leads came in(Column E). Now I need to track this data on a monthly basis by sales person.

    Thanks!

    ReplyDelete
  5. Hi K:

    I suggest you share your Google spreadsheet

    1) with some sample (but realistic) data
    2) tell us what you are trying to compute
    in which cell?
    of which sheet?
    3) tell us what is your expected result
    along with needed logic/explanation as to why that is the correct result

    and then let us take it from there.

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  6. Hi Yogi -

    Below is a sample of the data I'm working with in Excel, the "DC Sales Leads" tab I mentioned above.


    Column A Column B Column E
    Salesperson Status Date Lead Came In
    Dan No Sale 4/2/12
    Dan No Sale 4/4/12
    Bobby No Thanks 4/4/12
    Liz Open 4/4/12
    Bobby No Sale 4/5/12
    Dan No Sale 4/6/12
    Liz Open 4/6/12
    Liz No Sale 4/6/12


    The new report I'm trying to create is to track how many leads each sales person handled each month. In Excel I used COUNTIFS to make the new report by month, but Google docs doesn't support COUNTIFS so I'm now looking for an alternative.

    Below is a sample of the new report I created in Excel pulling from the data above and using COUNTIFS, but I can't get it to work in Google Docs.


    Number of New Leads Per Sales Person by Month

    Column B Column D Column E
    Sales Person Jan-12 Feb-12

    Dan 15 24
    Bob 23 31
    Liz 26 28


    Thank you for reading through all this! I know it must be hard to help when you don't have all the context behind the posts.

    ReplyDelete
  7. Ok sorry, clearly I'm a newbie with posts so please bear with me, I lost all the formatting/spacing above.

    Real quick to clarify:
    In the first set of data Column A is "Sales Person, Column B is "Status" and Column E is "Date Lead Came In".

    In the second set of data, Column B is "Sales Person", Column D is "Jan-12" (this column totals up the number of leads in January for each sales person), and Column E is for "Feb-12".

    Even though the data is all squished together I think you can get the idea.

    Thanks for your patience....

    ReplyDelete
  8. Hi K:

    As I said in my earlier post ...

    I suggest you share your Google spreadsheet

    1) with some sample (but realistic) data
    2) tell us what you are trying to compute
    in which cell?
    of which sheet?
    3) tell us what is your expected result
    along with needed logic/explanation as to why that is the correct result

    and then let us take it from there.

    If you do not want to share your spreadsheet in Google Docs Help forum and you want to reach out to me outside the Google Docs Help forum, contact me via www.vCita.com/yogi.anand


    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. https://docs.google.com/spreadsheet/ccc?key=0AqFQimVCPLK_dDBqYmtHX055OXVPVFRMR2lwTng5VFE#gid=0


      What I want is the count of Network Incidents closed. Can you please help me?

      Delete
  9. Hi K:

    I have presented an illustration for solution to the problem in my following blog post:

    yogi_WorkAround For COUNTIFS In Excel For Multi
    http://yogi--anand-consulting.blogspot.com/2012/11/yogiworkaround-for-countifs-in-excel.html

    Please check it out to see how this works for you.

    Cheers!
    Yogi

    ReplyDelete
  10. instead of "Group 1" it should count non-blank cells

    ReplyDelete
  11. Hi prabhakaran kn:

    Please explain clearly what exactly you want to compute ...

    1) what data
    2) formula in which cell?
    of which sheet?
    3) show me your expected result
    along with needed logic/explanation as to why that is the correct result

    and then let us take it from there.

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

    ReplyDelete