Count value types in a column of data

There are times when you want to know how many negative or positive values there are in a column. When would you want this information, well who knows but I bet you will sleep better know how to do it.

You want to use the COUNTIF function. You simply identify the column of data that you want to count and then for the criteria put >0. Now in order for this to work, you need to put quotes around >0.

And I bet by now you have guessed that you could count the negative values just by reversing the >0

=COUNTIF(B:B,">0")     Returns the number of positive numbers in a range
=COUNTIF(B:B,"<0")     Returns the number of negative numbers in a range

BEGINNER, COUNTIF

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