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

No comments:

Post a Comment