Business

Google Spreadsheets: COUNT with Multiple Values in Cells

It’s been more than two years since I wrote a post on using Google Spreadsheets to use COUNT and FILTER in tandem to count values with multiple conditions.

Circling back, it does very well at allowing you to count when you need to validate multiple conditions. However, it falls apart the minute that your data is either messy or comes grouped with other data inside of a cell. Here’s an example where my original post would have trouble with:

Google Spreadsheets: Multiple Values in Cells

Why does it fall apart? Each cell can have multiple values inside of it.

To dig a little further, we have to understand that my original post called for using the FILTER function to pare down the data set into the specific rows that we need, and then using the COUNT function to count the result of the filter. However, FILTER does not allow for wildcards in its conditions for filtering, making it extremely difficult to account for data that is in the same cell as other data.

Luckily, there’s an easy way to get around it, using a crude but workable solution for counting values with multiple conditions, with sources that have multiple values in cells, extensible to messy or not-very-nice data.



style="display:inline-block;width:728px;height:90px"
data-ad-client="ca-pub-9705548764325874"
data-ad-slot="9050629881">

The Scenario

In the above example, let’s say we’d like to count the number of items inside of Column A and Column B for every row that Item A appears in.

Manually counting them, we know that for Item A, it is listed on a total of 3 rows (Rows 2, 3, and 7) and has 7 values listed to its name in Column A, and 4 values listed in Column B. Hopefully I haven’t lost you, but I’m saying that we count the number of values inside Column A every time Item A is listed, and we do the same for Column B.

How would we go about doing this in Google Spreadsheets?

For the impatient, here’s what the formula looks like:

=COUNTA(SPLIT(JOIN(",", FILTER(B:B, SEARCH("*A*", A:A))), ","))

I know, I know, it looks odd and confusing, so let’s break it down starting from the very inside formulas.

=SEARCH("*A*", A:A)

This is what we’re using as a condition inside of FILTER. SEARCH allows us to parse through the values in a column or row and find characters or values that we’re looking for. In this particular instance, I am looking for the character “A”, but you could be looking for whole words, sequences of characters, you name it, and you have the possibility of adding wildcards on the outside of the term you’re looking for to make it catch every instance properly. Experiment with this, because I’m not 100% sure on whether or not the wildcards help in every situation.

=FILTER(B:B, SEARCH("*A*", A:A))

When SEARCH is used in conjunction with FILTER, it returns a list of rows or columns (as specified by the first parameter in FILTER, in this case it’s B:B) that returned a TRUE condition for the SEARCH. The result would look like this:

X, Y, Z
X, Y, Z
Z

This is returning B2, B3, and B7 respectively from my above example, because they had Item A in their Item column.

=JOIN(",", (FILTER(B:B, SEARCH("*A*", A:A)))

Using JOIN, you can combine all of our FILTER results into a single cell, where each cell’s values are separated by a comma (first parameter from JOIN, in this case “,” means separate values using a comma) and makes it nice and simple for us to analyze just this one cell.

X, Y, Z,X, Y, Z,Z

This is what our data now looks like, all inside of a single cell.

=COUNTA(SPLIT(JOIN(",", FILTER(B:B, SEARCH("*A*", A:A))), ","))

We arrive back at the original Google Spreadsheets formula, with SPLIT and COUNTA appearing. SPLIT allows us to break apart a single cell into many cells that contain each and every value that we have in the cell, as long as we let it know how the data is separated. In this case, it’s separated by commas because of the work we did above with JOIN, and we use COUNTA to count the non-numeric values that the data represents.

Doing this for Item A, we can quickly see that it has 7 items in Column A, and 4 items in Column B.

Easy peasy eh?

I linked the functions into the documentation for Google Spreadsheets as I listed them above, but here they are again, just in case:

If you end up trying this out, let me know what you think, or if you have any questions, or if you have any improvements or suggestions!

“We do enforce this policy.”

I run a very small, very casual video games channel on YouTube called The Blundercast – I just record, edit, and post silly little moments that happen while I play games. It’s very much a labour of love, but I do happen to monetize a few videos just for a bit of coffee money here and there.

Most recently, I posted a video where I played Scribblenauts Unlimited and had fun on a mission.

I did attempt to monetize this video but was abruptly stopped by YouTube.

YouTube:
We may consider your video(s) for further review provided you verify that you are authorized to commercially use all of the elements of your content. This includes all video, images, music, video game footage, and any other audio or visual elements.

Fair enough, I’ve run into this before. I explained:

Me:
This video is a video where I have fun with a small portion of the Scribblenauts Unlimited game. It was created solely for the purposes of entertainment and education and is all done in fair use.

Makes sense to me, you learn about the game and you can enjoy watching me make an ass of myself on the internet. However, it got rebuffed with a request for information regarding formal permission and/or terms that would allow me to post the video.

I reached out to WB Games, the publisher of Scribblenauts Unlimited, to get this permission, and got this response in a few hours:

WB Games Support:
WB does not provide formal permission to post videos on YouTube or similar sites. Generally we don’t mind fan videos so long as you’re using legal copies of the game, are not being posted to make a profit (through advertising or other means), and are in good taste.

Hmm… not being posted to make a profit? What about the hundreds of videos that do just that on YouTube? Do they all have a standing agreement with WB Games that allows them to post and profit off their videos? Or are they in danger of having WB enforce their policies on them?

So I asked to clarify, especially with regards to YouTube partners, and got this response:

WB Support:
WB does not give out any formal permission. We also do enforce this policy.

And now we’re back at square one.

I understand you want to protect your game, but we’re giving you free marketing at no cost. I’m not entirely sure why you would be against that?

It is a silly place.

Stop using social channels to push, start pulling.

It feels incredibly weird that I still have to say this near the end of 2012, but stop using your social channels to solely push content.

Yes, you have a captive audience. Yes, they want to hear what you say. Yes, they will retweet and like and re-pin every trivial thing you push out there.

But why aren’t you listening to them?

If you follow me on twitter, and actually manage to catch me tweeting, you might know that I’ve been disappointed with GoPro and their lack of responsiveness.

Now, I’m a huge fan of GoPro. I have loved their products since the very start, and finally bought one with the announcement of the HERO3, simply because it could shoot 720p video at 120fps. Seriously, that’s all I wanted.

Well, I received my GoPro HERO3 Black Edition on Wednesday, November 14, 2012, and I spent the better part of that night playing with it. Learning the ins-and-outs, I quickly became accustomed to it and so I started to connect it to my accessories – no problems with the wi-fi remote that came with my HERO3, but any and all instructions on connecting to the GoPro iPhone app, which was pimped out on my packaging and all over their website, were for the HERO2 + Wi-Fi BacPac, and even a manual firmware update didn’t do a thing.

What?

So I tweeted GoPro, thinking I’d get a quick response (they were tweeting at least once an hour at that point) but it never came. I tweeted again the next morning, thinking business hours would catch them, but… nothing.

It turns out, GoPro doesn’t respond to anyone. At all. Not on Twitter, not on Facebook, nothing. Hell, right now, I’ve been intercepting messages to their Facebook page and answering the questions that pop up about the HERO3 Black Edition and connecting to the GoPro app.

NOTE: The solution, if you were wondering, is that there is none. The HERO3 Black Edition won’t be getting a firmware update until December 14, 2012. That bit was buried in their features page for the HERO3, so I can’t complain about not being told. However, it still sucks that the packaging lead me to believe otherwise, but I can’t be mad about it.

I sent them an email to plead with them to engage with their audience. They may ignore it, and that’s most likely going to happen.

However, if you run any sort of consumer-facing company and you still don’t get it: PLEASE engage with people on your social channels.

I’m not asking you to respond to trolls and haters (though it’s an opportunity to swing them into your favour with your great responses) but I am simply asking you to fully utilize these channels that people come to have their questions answered and even just tell you how awesome you are. Plenty of companies are doing this correctly, and even if you’re the market leader in the industry (looking at you, GoPro) you should do everything in your power to build walls against any eventual competitors that come your way.

Stop just pushing, start pulling.

FreshBooks + Stripe = Freelancer’s Best Friend

As an occasional freelancer, one of the more painful aspects in my day-to-day operations is the invoicing and payment collection.

Now, I’ve used FreshBooks for a good long time for the freelancing side of the business, and it has always served me well – keeping track of billable hours and expenses – but I’ve always found payments to be a tad annoying. The previous payment gateway I used was Paypal, simply because there weren’t any other useful alternatives.

And then, Stripe came to Canada.



style="display:inline-block;width:728px;height:90px"
data-ad-client="ca-pub-9705548764325874"
data-ad-slot="7669798281">

Oh happy days! Stripe makes online payments with credit cards much, much easier, and when coupled with FreshBooks, it makes the collection of payments for invoices that much easier as well. Payments can now be made directly inside of the FreshBooks interface, and the payments will go directly to your bank account rather than sitting inside of Paypal.

To get started, you’re going to need an account for both FreshBooks and Stripe. Here are the sign-up links for those interested:

FreshBooks (Affiliate link!)
Stripe

Once you’ve signed up for both services, go to Stripe and click on the “Your Account” button in the top right and head to “Account Settings” and navigate to the “API Keys” section of the settings popup.

API Keys from Stripe

You’re going to need the “Live Secret Key” in a minute, so keep it handy.

Next, click on “Settings” in the top right part of your FreshBooks account, navigate over to the “Online Payment” page, and click the box next to “Stripe”. A new section should appear asking for your “Live Secret Key”. Paste it in here, click “Save”, and you’re good to go!

Now, when clients want to pay off their invoices online using a credit card, they’ll see this button at the top of their invoice:

Stripe Invoice Payment

And clicking on it will bring up this payment form:

The real advantage, in my opinion, is that I can now control the entire branding experience, rather than having clients taken away to Paypal and then being directed back. The flow of payments is far easier, and you can still leave Paypal as an additional option if your clients really need that.

Happy freelancing!

Being Lost

Hi, my name is Jon, and I’m a bit lost in life.

*pauses for greetings*

I’ve spent the last month flip flopping between ideas, contemplating re-joining the working force, and generally being unhappy. I started September with the idea that I could take a serious attempt at becoming a good developer and building a web product that would give me some level of monetary comfort. Progress was good, until one day I sat down and just did not feel right.

I beat myself up for pursuing ideas purely for the sake of a piddly amount of money, ideas that did nothing to better the world. In short, I felt that I was selling myself short and I was underachieving.

From there, I set out on the hunt for ideas that could make a meaningful impact on the world, to society, or at least better the lives of those who really needed it. However, this started this rather vicious cycle of falling in love with an idea, realizing that I don’t have any sort of expertise in this space, and becoming dejected at the idea of being “just the business guy” in a venture, and then being sad and frustrated that I haven’t figured things out yet.

Right now, there’s no happy ending in sight.

So why would I bother writing all this out and telling people? Because, as awesome person Guy Gal told me when I told him this story randomly: “Success is all in the recovery.”

Things will get better. Eventually. And no, it won’t be because I wait patiently for an idea to strike home; things will get better because I will work hard to create opportunities for myself. I am going to go out there and pound the pavement, meeting new people and learning about problems, and taking a crack at creating the solution. It’s going to be disheartening, frustrating, and difficult.

But who said success was going to come easy?

On My Actual New Venture

Yesterday, I wrote a post, On New Ventures and Making the Right Decisions. I received an unexpected amount of feedback because, well, it sounded like I was starting a new venture and was announcing it to the world.

Well, I am, but I didn’t mean to use that post for that. Darn.

To the many people who took the time to read the post and personally reached out to offer their support: thank you. A million times thank you to Dessy, Brian, Duane, Jay, Aleks, Rob, and Ray for being the kind and supportive souls that they are.

However, I figured I should probably elaborate a little more on what’s going on.

That sounds like a good idea.

The Next Three Months

Make no mistake, I am definitely starting something new, but a business may not sprout for the next couple of months.

The reason is that when I left my last job, I told myself that I’m going to take a serious crack at making a product. Never mind what, I needed a way to build it in the first place, so I’m taking the next three months to seriously learn development. Seriously.

I’m starting with Python because I’m familiar enough with it (thanks to first year university comp sci and Codeacademy) to get started quickly enough, and it’s a popular enough language now that there are plenty of resources to learn from should I get stuck.

So how will I be learning?

I will be creating small apps that solve little problems I have, or are just plain cool.

Yes, seriously. The first thing I’m working on is a script that connects to the Last.FM API to pull my entire play list throughout the years (approaching 50,000 plays over four years!) to categorize them by genre and separate by the year it was played in. I just want to visualize the change in my musical tastes over the years.

That’s it! Nothing entirely special, but it will give me a good foundation to start with using Python, including figuring out what functionality each piece of the script will need as well as the libraries I’ll need to import to even connect to an API.

I budgeted this week to code and design the main parts of the app, but I refuse to determine the app for the second week because I do not know exactly how much time I need for this. However, I will basically be building apps on a weekly basis until I get to the point where I can create a much larger scale project and feel comfortable taking it from start to finish.

In The Bigger Picture

Three months isn’t a lot of time to accomplish much (despite what my wallet thinks) but I figure it will also give me three months to create a project that I will be happy to work on for the foreseeable future and has a sustainable business model from the start. I’m not entirely sure what it will be yet, and that makes me a bit nervous, but we’ll tackle that problem when we get there.

These three months will be a time for the creation of a solid development process as well as a solid idea generation process. It would be foolish to think that an idea is just going to find me and smack me over the head.

At the end of the day, I just want to spend every single day of my life working on projects that excite and challenge me. This is the beginning of the foundation that I am building.

The unknown can be a tad frightening and intimidating. I can’t say I’ve made the right decision right off the bat, but I know that this is the decision I wanted to make anyway. Besides, what’s the harm in venturing into the unknown?

Let’s go exploring.

On New Ventures and Making the Right Decisions

For the past week, I’ve been struggling to verbalize (on paper, mostly) how I feel about making the plunge back into starting my own venture. It’s a life filled with highs and lows that always makes you question everything about what you’re doing, so it can be a bit difficult to write about.

Well, as with everything else in life, someone else wrote it better than I ever could.

In a thread on Hacker News where someone announces that they’ve done the scariest thing they can imagine: resigning from their job and starting their own company in the Netherlands, Ed Weissman posted this wonderful comment on what’s to be expected:

The good news: If you think you’ve done the right thing, then you have. Congratulations!

The other news:

  • Your runway is probably 50% of what you think it is.
  • Your task is probably 200% of what you think it is.
  • It will take 200% of what you expect to get your first customer.
  • Your wife will get scared before you do.
  • Some customers will take forever to pay.
  • You’ll probably have to backtrack on your design / architecture.
  • When you need to sell, you’ll want to code.
  • When you need to code, you’ll want to sell.
  • Your MVP will be missing something critical.
  • At some point, you’ll question your decision.
  • At some point, someone will discourage you.
  • You may need to pivot your entire business.
  • In a year, you won’t be who you are now.

I don’t mean to discourage you. I just want you to bottle that enthusiasm for later use. You’ll probably need it.

Best wishes!

Here we go.

Why Can’t I Focus?

I take pride in being realistic in my abilities, my goals, and my confidence. I live my life with this knowledge: I am probably not smarter than you, I am probably not stronger or faster than you, but I make sure that I do my best to work harder than you. However, working harder than someone else doesn’t necessarily translate into doing better than them, or even doing better than mediocre levels of success.

I think that most people are driven to succeed. They want to provide for themselves and their family, and feel proud of their accomplishments at the same time. Myself included. However, we also want to create balance for important aspects of our lives like family, friends, love, and extracurricular activities like sports and hobbies.

Given that we usually use 8-10 hours a day for “work,” how can we maximize what we accomplish within those hours?

Throughout my years of attempted greatness, there has been one thing that people tell me to do, every single time without fail: focus. It usually comes in a few different flavours: hustle, stop slacking off, be relentless, focus on one thing and one thing alone. You get my drift.

Every time I hear this, I cringe and remember why I don’t succeed at a lot of things: too much aimless focus. I have an idea, get really excited about it, pour on lots of energy into building the foundation for it and simply fizzle out as soon as the work starts to happen.

Sound familiar?

If I were to go back in time and have a talk with younger me, I would tell him this: everyone will tell you to focus, to put your head down and get shit done. It’s important that you do, but the step before focus? Planning.

You can focus all you want, but remember that if you have nothing to focus on, you’re basically ramming your head into the wall and hoping that something comes out for you to focus on. You need to be able to wake up in the morning and say “Alright, today is awesome, here’s what I need to do for today.” because you’ve planned the entire thing out months and months (maybe even years) in advance. After all, how can you put your head down and focus if you’re just making it up as you go along?

Time Management from xkcd
From: xkcd – Time Management

I recently left my job (despite my short time there) with one goal in mind: build something. As vague as that sounds, I wanted the freedom to work on something fun and challenging, that puts food on my table, at my own pace, my own schedule, on my own terms. That’s the long term goal.

To get there, however, I need three things: the ability to build, the ability to design, and the right ideas to build. Given that, I’m in the process of creating a three month curriculum for myself to become a better developer (I’m currently terrible), a better designer (couldn’t design my way out of a box), and to spend ample time creating a process to brainstorm the right ideas to build. In addition, I’ve set a demanding set of goals that I must accomplish by September of 2013, exactly one year from when I start my curriculum.

The important part of this whole process is that given the long term goal (build something fun and challenging, put food on the table), I created a goal for the medium term (one year), and then I created a month-by-month set of goals that incrementally get me to the goals I set for one year, which then spawned a week-by-week set of goals that get me to the monthly goals.

At the very worst, I will have given myself plenty of time to learn new skills, hone my craft, and take a serious crack at “building something.” After all, now that I know what I need to do for the next year, the next logical step?

Focus.

WTF, Amazon. What The (Price) Fix?

Hi Amazon,

I love to read, and so it should come as no surprise that I am a huge fan of your Kindle. It’s wonderful: it’s portable, I can load it with thousands of books, and the battery life is amazing. It’s the device that every reader should seriously consider.

However, here’s what I am not in love with: the ridiculousness of e-books in your store.

I understand that e-books may or may not be cheaper than the paper versions, for whatever reason, but people have to feed their families. I understand.

What I don’t understand is, what the heck are you trying to pull with us loyal customers?

Background: I am in the middle of reading this amazing series, The Malazan Book of the Fallen by Steven Erikson, and I just finished book seven: Reaper’s Gale. Despite being the longest series I have ever read, the books have managed to keep me captivated thus far and I was very excited to start the next in the series, Toll the Hounds.

There was a link, at the end of the Kindle version of Reaper’s Gale, to go buy Toll the Hounds in the store, so I clicked on it and noted the price for the book was $12.65. Wanting to make sure that the price was correct, I go to my computer and look at the listing, and I can confirm that it is the same price.

Oh okay, everything checked out.

However, I am a rather cautious fellow. I opened up Incognito mode in Chrome and Googled ‘amazon toll the hounds kindle’ and… what’s this?

Hold on, why was there an option that I was never shown? Why was it significantly cheaper than the one shown to me, someone who has bought 48 e-books in the past year and a half that I have owned my Kindle?

Here are the key differences between the two listings:

  • Cover Image
  • Publication Date

They are virtually the same otherwise. Just to prove it, I sent myself a sample of the more expensive version after I bought the cheaper, older version. Older version on the left, newer version on the right.

Can you spot the actual difference? Yup, the font.

So Amazon, please answer me this: as a loyal customer, why would you want to sour my experience with this practice?

UPDATE: It was pointed out on Reddit that, in the area that shows all of the different types of mediums that the book is purchasable on (Kindle, Hardcover, Mass Market, etc), you can click on the plus sign next to the medium type to get an expanded list for that particular medium.

As I responded on Reddit, I consider myself rather technically capable and observant, and I still had no idea that existed. Now you know!

Canadian Streaming TV Apps: Report Cards!

Television watchers in Canada usually get the short end of the stick when it comes to watching shows, on-demand, online. If we aren’t being restricted because we don’t live on American soil, then we’re being restricted for not having a specific type of account.

To give you an idea of what we’re dealing with, I spent a bit of time with four of the only streaming TV apps that work well for *any* Canadian who has an iOS device or a computer. In addition, I’ve tested out how each of the apps handles AirPlay and what happens to the quality of the video upon doing so; a useful metric for those of us who own an Apple TV.

NOTE: If you want to see the combined report cards, go directly to the Canadian TV Streaming App Report Card page!

 



style="display:inline-block;width:728px;height:90px"
data-ad-client="ca-pub-9705548764325874"
data-ad-slot="1112331084">

I visualized the data into a “report card” of sorts to give you a side-by-side comparison of features and offerings. Here’s how to read the report cards:

Canadian Streaming TV App Report Card Reading Instructions

  1. App Info
    The name of the app and who it is published by. This is also an indicator of which of the major media companies in Canada happens to own this channel and app.
  2. Device Availability
    From left to right: iPhone, iPad, Web, and Airplay. Certain apps are only available on certain platforms. If it is not available, that given platform will be greyed out, such as AirPlay in this instance.
  3. Measurement Minimum
    For the given section, in this case the “Number of Shows,” this is the minimum number for the measurement. For both instances, it will be zero.
  4. Measurement Maximum
    For the given section, in this case the “Number of Shows,” this is the maximum number for the measurement. This is determined by the highest number I encounter while measuring.
  5. Measurement Average
    For the given section, in this case the “Number of Shows,” this is the average number measured across the board.
  6. Video Quality Indicator
    For a given device, I took a look at the video quality available for a user across multiple shows and made my judgment. This is rather subjective, but if it is passable quality (not full of artifacts and jaggies) then it is given a checkmark. An ‘X’ will only be given if it is not available in the first place, and/or it is of very poor quality.
    NOTE: Quality, for most of these apps, was great on iOS devices and so-so on the web at full screen.
  7. Advertisement Usage
    An indicator of when and where advertisements show up, in relation to watching a television episode. Some apps, such as the CTV app, will actually interrupt you as you are using the app and looking for a show to watch.

General Comments

While using these apps, it became increasingly apparent that “on-demand” was a term that could only be loosely used by these apps. There seems to be a general trend, with web content from large media companies, where they will only store a handful of the most recent episodes (if that) and the rest are nowhere to be found. In fact, CTV had the highest show count of the four apps, but had the lowest “average number of episodes available” count because they just had so many shows that had zero video content.

In addition, there can be some misleading content in these apps. For example, I don’t believe a collection of your season finale episodes count as a “show,” CBC. And 245 videos labeled “Season X, Episode Y,” but are actually just 2 minute clips, do not count as episodes, CTV. Not to mention, certain platforms do not show all content actually available; CBC had a handful of shows only available on their website but not on the CBC TV app.

With “on-demand” apps like this, it would be a great way to reduce piracy of your shows while receiving some advertising revenue. However, you are forcing consumers to either purchase DVDs (which I assume is the reason to not put full catalogues online) or to pirate shows. I would suggest a re-evaluation of how content is provided to consumers, because you are only making it more difficult for us.

Additional Notes

As I mentioned in the opening paragraphs, I only spent time with these four apps. For the sake of full disclosure, you should know that there should have been a fourth: Rogers Anyplace TV. There were two reasons, on my part, to exclude a valid television streaming service: 1) you need a working Rogers account to access the site, which not all Canadians have. 2) even if you have a Rogers account, the tablet app is restricted from you if you are on a monthly plan.

Rogers Anyplace TV

That made no sense, so they were not included.

In addition, I do not hold any of the copyrights for the app icons used in the report card, they are owned by their respective media companies.

You can also find the data I collected here. It contains all of the shows that can be found in the apps themselves, along with episode numbers, and any notes I may have made along the way.

I am happy to answer any questions that might come up, as well as add analyses of any apps that I may have missed.

Thanks for reading!

NOTE: If you want to see the combined report cards, go directly to the Canadian TV Streaming App Report Card page!