Solved

Countif in Excel

Posted on 2012-04-04
13
180 Views
Last Modified: 2012-04-11
Attached I have a file for an example. Column C has long list of data such as "2, 6, 10". Column D has then just one number such as "2". I would like Column E to do a countif of total cells that say Column D's number in it. Any idea how to do this? Attached is a file
Practice-Sheet.xlsx
0
Comment
Question by:cansevin
  • 6
  • 5
13 Comments
 

Author Comment

by:cansevin
ID: 37807570
Would it be easier if the data was broken up into different cells? So, instead of a cell with "2, 4, 7" it would be "2" then next to it "4' then next to it "7"?
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 37807581
Try array entering a formula like:
=COUNT(FIND(" " & D2 & ","," " &C:C  & ","))

To array enter the formula:
1) Paste the formula in the formula bar
2) Hold Control and Shift keys down
3) Hit Enter
4) Release all three keys. Excel will respond by adding curly braces { } surrounding your formula.
0
 
LVL 13

Expert Comment

by:Ashok
ID: 37807600
Would it be easier if the data was broken up into different cells? So, instead of a cell with "2, 4, 7" it would be "2" then next to it "4' then next to it "7"?

Yes
0
 
LVL 13

Expert Comment

by:Ashok
ID: 37807775
If you separate numbers, it will work like this...

see attached file
Practice-Sheet-New.xlsx
0
 
LVL 81

Expert Comment

by:byundt
ID: 37808405
With all due respect, the accepted solution is dreadful.

It is not designed to be copied down, and contains mistakes in logic besides:
=COUNTIF(G2:J2,D2)+COUNTIF(G2:J3,D2)+COUNTIF(G4:J4,D2)+COUNTIF(G5:J5,D2)+COUNTIF(G6:J6,D2)+COUNTIF(G7:J7,D2)+COUNTIF(G8:J8,D2)
The second COUNTIF should use G3:J3 instead of G2:J3

If you are going to go to all the trouble of eliminating the extra spaces, then using Data....Text to Columns to split the data into separate cells, you ought to take advantage of the full capability of COUNTIF function to look at multiple columns. COUNTIF can even check columns to the right of the range with the limited set of sample data.
=COUNTIF(G:K,D2)              

I've posted a sample workbook showing how the suggested array formula works with the data in the original question, as well as the revised COUNTIF formula.

Brad
Practice-SheetQ27662561.xlsx
0
 
LVL 13

Expert Comment

by:Ashok
ID: 37808608
The formula is not desined to copy down.  That is correct.

=COUNTIF(G2:J2,D2)+COUNTIF(G3:J3,D2)+COUNTIF(G4:J4,D2)+COUNTIF(G5:J5,D2)+COUNTIF(G6:J6,D2)+COUNTIF(G7:J7,D2)+COUNTIF(G8:J8,D2)

and

The second COUNTIF should use G3:J3 instead of G2:J3.   Thanks for point it out.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 81

Expert Comment

by:byundt
ID: 37808912
ashok111,
According to the original question <<Column C has long list of data>>. The Accepted Solution, however, uses one COUNTIF for each row of data in column C. With only seven rows of data in column C of the test workbook, I admit that one can write a brute force formula using seven COUNTIF functions. But the lack of $ dollar signs on the search range means you can't copy the formula down, letting the reference to cell D2 update, but keeping the row references fixed for columns G through K.

Furthermore, the Accepted Solution doesn't scale. If you have hundreds of rows of data the formula gets so long that you can't find a typo if one exists. Worse yet, you eventually reach the maximum allowable number of characters in an Excel formula--255 in Excel 2003 and 8192 in Excel 2010.

The above issues are why I was left spluttering when the question was closed.

I agree that splitting the data the way you have done offers some significant benefits. I also agree that COUNTIF is the right type of formula to use on the data once it is split. Where we differ is how to set up that COUNTIF.

Brad
0
 
LVL 13

Expert Comment

by:Ashok
ID: 37809216
Brad,

Here is real solution that could be easily copy and pasted with CountIF().

Create a Named Range called "Test1" by selecting G2:J8 (all cells) and naming it "Test1", then

Use following in Row # 2.....

=COUNTIF(Test1,$D2)

and copy and paste above in all following rows (from Row # 3 to Row # 12)

HTH
Ashok
0
 
LVL 81

Expert Comment

by:byundt
ID: 37809222
Ashok,
I agree 100% with the direction of your latest suggestion.

Brad
0
 
LVL 13

Expert Comment

by:Ashok
ID: 37814811
http:#a37807775 is a functional solution (what I mean is that it works) except that it had 1 character mistyped,

Ashok
0
 
LVL 81

Expert Comment

by:byundt
ID: 37814852
Ashok,
With all due respect, http:#a37807775 is a valid solution only for the sample workbook posted by cansevin. It cannot possibly be used for the real problem of picking winning lottery numbers given a column C full of numbers chosen in previous draws (or whatever it is that cansevin is doing).

Your suggestion in http:#a37809216 is a much more general solution to the problem. Absent more pertinent feedback from cansevin, I believe it will work perfectly in the Asker's real workbook. If this question is referred to by another person needing help, '216 is the Comment you want to suggest he follow. And I'd be completely OK with making that Comment the Answer to this Question.

Brad
0
 
LVL 13

Expert Comment

by:Ashok
ID: 37816811
Brad,

I downloaded your excel file from http:#a37808405 and looked at it.
Practice-SheetQ27662561.xlsx

The revised formulas look good to me.
For example

=COUNTIF(G:J,D2)

In above, you removed the row # (and it does not require multiple CountIf(s)) which makes it possible to copy & paste it.

Thanks,
Ashok
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now