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