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
