### More than one way to solve a problem

I am often amazed at how people use Excel to solve problems. Actually, what amazes me is how people can create complicated solutions to solve simple problems.

Case in point. A co worker of mine prepared a spreadsheet that counted the number of times an item appeared in a list. The formula they used worked but it struck me as overkill. Here is a sample of their solution.

They had an item list in column A that had item numbers repeated throughout the list. They also had a master list of items in column c. This master list had only 1 of every item number in column A. In column D they calculated how many times the item in column C appeared in column A. They combined the SUMPRODUCT and EXACT functions to get their answer and it works.

 A B C D E 1 Item List Item # Count Formula 2 1 1 3 =SUMPRODUCT(--EXACT(A:A,C2)) 3 2 2 4 =SUMPRODUCT(--EXACT(A:A,C3)) 4 3 3 2 =SUMPRODUCT(--EXACT(A:A,C4)) 5 2 4 1 =SUMPRODUCT(--EXACT(A:A,C5)) 6 5 5 2 =SUMPRODUCT(--EXACT(A:A,C6)) 7 4 8 1 9 2 10 3 11 1 12 5 13 2

When I inquired as to why they didn’t just use the countif function, they replied, “what’s that?”

It dawned on me that they didn’t know the countif function and developed a work around using what they did know. The more I thought about it, the more I came to realize that isn’t that what each of does with Excel? We use what we know. One of Excel’s strengths lies in its flexibility for solving problems in a multitude of ways.

So what was my recommended way of solving the above problem? I used the count if function in column H to return the same results.

 A B C D E F G H I Item List Item # Count Formula Item # Count Formula 1 1 3 =SUMPRODUCT(--EXACT(A:A,C2)) 1 3 =COUNTIF(A:A,C2) 2 2 4 =SUMPRODUCT(--EXACT(A:A,C3)) 2 4 =COUNTIF(A:A,C3) 3 3 2 =SUMPRODUCT(--EXACT(A:A,C4)) 3 2 =COUNTIF(A:A,C4) 2 4 1 =SUMPRODUCT(--EXACT(A:A,C5)) 4 1 =COUNTIF(A:A,C5) 5 5 2 =SUMPRODUCT(--EXACT(A:A,C6)) 5 2 =COUNTIF(A:A,C6) 4 1 2 3 1 5 2

So how would you have solved the above problem? Do you have a different solution then the ones presented above? Break out your mad crazy Excel sills and let us know.

Beginner, CountIf, SumProduct, Exact