countif counter

Asked by: voccoe

Hi,
I need the countif function as a macro in which one macro calculates the number of 5s, 4s, 3s, 2s, 1s and total count of one coloumn. (coloumn C)

Sheet name = Score summary

I want something like:

If cellvalue <> then
      Counttotal = counttotal +1;

If cellvalue = 1 then
      Count1 = count1 +1
      End If

If cellvalue = 2 then
      Count2 = count2 +1
      End If

If cellvalue = 4 then
      Count4 = count4 +1
      End If

If cellvalue = 5 then
      Count5 = count5 +1
      End If
End If

Now i want to tell macro to put counter results in adjacent cells. (in a table) on Score Summary sheet.

Count1
Count2
Count4
Count5
Counttotal

Conttotal is integer
Count1 is integer
Count2 is integer
Count4 is integer
Count5 is interger
 
Couttontal = 0
Count1 = 0
Count2 = 0
Count4 = 0
Count5 = 0
 
If cellvalue <>  then
	Counttotal = counttotal +1;	
If cellvalue = 1 then
	Count1 = count1 +1
	End If	
If cellvalue = 2 then
	Count2 = count2 +1
	End If	
If cellvalue = 4 then
	Count4 = count4 +1
	End If	
If cellvalue = 5 then
	Count5 = count5 +1
	End If
End If

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:

Select all Open in new window

Instant Access to this Solution

Plus...

30 Day FREE access, no risk, no obligation

Collaborate with the world's top tech experts

Unlimited access to our exclusive solution database

Never be left without tech help again

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

"The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.

"Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin

"Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

voccoe,
The easiest way of doing this is with the COUNTIF formula itself. If put a stop in the code at statement 7, you can see how the formula is doing the work.

If you really need to loop through the cells, could you give a bit of explanation as to why? If this is a homework assignment, I don't want to cause you any difficulty with the prof.

To install a sub in a regular module sheet:
1) ALT + F11 to open the VBA Editor
2) Use the Insert...Module menu item to create a blank module sheet
3) Paste the suggested code in this module sheet
4) ALT + F11 to return to the spreadsheet

To run a sub or macro:
5) ALT + F8 to open the macro window
6) Select the macro
7) Click the "Run" button

Optional steps to assign a shortcut key to your macro:
8) Repeat steps 5 & 6, then press the "Options" button
9) Enter the character you want to use (Shift + character will have fewer conflicts with existing shortcuts)
10) Enter some descriptive text telling what the macro does in the "Description" field
11) Click the "OK" button

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

Brad

Sub Counter()
Dim i As Long
With Worksheets("Score Summary").Range("D2:D6")     'The count summary will go here
    i = .Row - 1
    .Formula = "=COUNTIF('Score Summary'!C$2:C$1000,ROW()-" & i & ")"
    .Cells(.Rows.Count).Formula = "=COUNTIF('Score Summary'!C$2:C$1000,""<>"")"
    .Formula = .Value
End With
End Sub

1:
2:
3:
4:
5:
6:
7:
8:
9:

Select all Open in new window

Question

countif counter

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Microsoft Excel Spreadsheet Software

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press