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
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
Thank you,
ReplyDeleteThe information you shared is very informative
Here’s what a business leader must know.
Power of Cloud Computing
I am glad you found the information in this blog post to be very informative ... Now Let Us Keep Googling.
ReplyDeleteCheers!
Yogi
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:
ReplyDelete=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?
Sorry, one clarification on the above post...
ReplyDeleteThe "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!
Hi K:
ReplyDeleteI 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
Hi Yogi -
ReplyDeleteBelow 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.
Ok sorry, clearly I'm a newbie with posts so please bear with me, I lost all the formatting/spacing above.
ReplyDeleteReal 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....
Hi K:
ReplyDeleteAs 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
This comment has been removed by the author.
Deletehttps://docs.google.com/spreadsheet/ccc?key=0AqFQimVCPLK_dDBqYmtHX055OXVPVFRMR2lwTng5VFE#gid=0
DeleteWhat I want is the count of Network Incidents closed. Can you please help me?
Hi K:
ReplyDeleteI 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
instead of "Group 1" it should count non-blank cells
ReplyDeleteHi prabhakaran kn:
ReplyDeletePlease 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