Ideas – they can come from another problem you’ve been working on

We're in budget season and I'm assisting my boss in training sessions with the site managers when an idea popped up. My boss was talking about a flaw in a formula that counted up employees. It was a COUNT formula that did not quite work due to the nature of the other formulas in the payroll tab. Sometimes you will want to give employees raises and this particular spreadsheet was set up to make it easier for managers to see the raises. The first row for an employee will show monthly wages before the raise and the row beneath it will show monthly wages after the raise. For example, say Mary was to receive a 5% raise in May. The first row will show Mary's monthly wage from January through April and the second row will show monthly wages at a new level from May through December. The problem arises when it comes time to count up the number of employees: the COUNT will double count each employee that will receive a raise.

When she pointed out the flaw and how to correct for that flaw, suddenly I thought, "Wait a minute. There has got to be a better way of doing that."

Normally, when I do counting and I want to count unique items, I will set aside a column (let's say column Z) that will count up all incidences of a name: =IF(COUNTIF($A$1:A23,A23)>1,"",COUNTIF($A$1:A23,A23)), where range A1:A500 is a range of names and A23 is the specific name you want to count. If the name has already appeared more than once, I will not put down a count number; I will use a blank instead. The reason for that is I will have another formula that will count up all of the ones to get a count of unique names. So the second, third or more occurrences of the name will be left as a blank. Note that in the COUNTIF, the A1 is locked as $A$1 and the second position A23 is left as relative. When you copy this formula, the $A$1 will remain fixed but the other will change. In row 1, the formula is =IF(COUNTIF($A$1:A1,A1)>1,"",COUNTIF($A$1:A1,A1)) and row 500 is =IF(COUNTIF($A$1:A500,A500)>1,"",COUNTIF($A$1:A500,A500)).

Now, when I want to count up the unique names, I will count up column Z: =COUNT(Z1:Z500).

But the file already has a lot of formulas and a lot of tabs (maybe over 20 tabs) so I was looking for single function or formula rather than a column of them.

I will admit that I didn't personally figure out another formula or function for counting but the "idea" consisted of another approach to solving problems. I wouldn't have thought of doing this if I hadn't been doing a lot of problem solving via this method. And I imagine most people I know don't think of doing this either. I don't see people doing this. I'm not sure why we don't use this method. Even the youngsters don't seem to deploy this. It might be the kinds of people that I'm surrounded with or the state I'm living in.

What is "this" problem solving method? It's called "Googling it". Yep, good ol' Google. I know, I know, maybe if I lived in California or was heavily ensconced in the IT industry, I would automatically use Google to see if the solution has already been thought of. But the fact is, I'm just not surrounded by people who automatically gravitate toward Google for answers. (Of course, most of them are probably not problem solving like I am).

The answer Google gave me was a site that provided formulas for counting - one for numbers and one for test. I still have to play around with it to fully understand what is going on but it looks like this: {=SUMPRODUCT(1/COUNTIF(A1:A500,A1:A500))}. I'm not 100% sure but my initial experiments indicate that the formula should be an array formula and not a regular formula as shown by the site. NOTE: array formulas are formed by hitting CTRL+SHIFT+ENTER to enter the formula rather than typing in the brackets {}.

Anyway, the point of this post is that ideas can come from another area that you've been working on. You probably won't think of it if you haven't been using it elsewhere, but once you start using that idea in one area, it becomes available to you in another. I think this is called "borrowing the idea". In my case, I've been using Google to find sites that explained how to convert Excel into XML or to find VBA codes for a specific action I want done in Excel. So having done a couple of Googling, it was natural for me to think of "why not Google to see if someone has already solved this." I pulled the idea of Googling from one area of problem solving into another area where I typically don't use it.