Counting Instances Of Item In A Column

  • Thread starter Thread starter Mr Milquetoast
  • Start date Start date
M

Mr Milquetoast

I have a table with data along the lines of the below.

A_______B______________C_______D
1_______Success/Fail_____Type_____Time
2_______S______________X_______34
3_______F______________Z _______45
4_______S______________Y _______43
5_______F______________X_______34
6_______S______________Z_______34

I want to work out the success rate for each type, which I thought
would be easy but has proven surprisingly difficult for an Excel
novice. Obviously to work out the success rate for type Z I would need
to count the number of Zs in column A and the number of successes S
corresponding to each Z and then divide. Seems simple but it’s a
problem that’s totally defeated me. Could somebody tell me how to do
this?

I have a second question if I might push my luck. I want to work out
the average time for a type so would need to sum up all the times
corresponding to the type and the number of instances of each type and
then divide. Seems simple enough but I can’t work out how to do it
with the Excel formula set. Anyone know?

Thanks a lot for your help.
 
Try the following...

Success rate:

=SUMPRODUCT(--($A$2:$A$6="S"),--($B$2:$B$6="X"))/COUNTIF($B$2:$B$6,"X")

...confirmed with just ENTER.

Average time:

=AVERAGE(IF($B$2:$B$6="X",$C$2:$C$6))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
The COUNTIF worksheet function might be useful to you, but it's limited
to one comparison criteria. A more powerful tool that will help you is
using the SUMPRODUCT function. There are a few good descriptions of how
it works, but it will look something like
=SUMPRODUCT(--(A1:A6="s"),--(B1:B6="z")) entered with ctrl-shift-enter.
This will count the number of times z and s correspond.
 
Thanks a lot. I'd been trying to use SUMPRODUCT but I was nowhere nea
a solution.

I'll try and put them into my actual table now
 
Thanks, they work great in my table.

Having a few more problems though. I’m really not very good at this
:)

I wanted to get the average of all values in a range greater than 20 so
I used:

=AVERAGE(IF(Results!AC73:Results!AC65000>20,Results!AC73:Results!AC65000))

Obviously the data is on a table called Results, but it doesn’t seem to
work. Should it?
 
Make sure that after entering your formula you confirm with
CONTROL+SHIFT+ENTER, not just ENTER. While holding the CONTROL and
SHIFT keys down, press ENTER. Excel should place braces {} around the
formula.
 
You can shorten it a bit:

=AVERAGE(IF(Results!AC73:AC65000>20,Results!AC73:AC65000))

buy you're probably not entering this as an array formula. To do so,
select the cell and type CTRL-SHIFT-ENTER.



Mr Milquetoast
 
Ah, thanks a lot. For some reason I thought the Ctrl+Shift+Enter wa
only needed for SUMPRODUCT rather than AVERAGE but I got it the wron
way around. Oops.

I haven't really used Excel other than to store information rather tha
do anything with it. I'll have to get a book and learn how to use i
better.

Thanks again
 
Thanks for all the help and Ken thanks for that link about Pivot Tables.
I think that would definitely be more appropriate for what I’m doing.
I’ll have to have a read of that later. JE McGimpsey, thanks also for
pointing out I don’t need the sheet name before each item in the range.
I think I’ll leave everything as it is for now but it will be good to
know if future.

I’m all done now but for one final statistic which I can’t get, so last
question I promise :) I want to get the average of some data that’s
between 0 and 100 values but this just gives 0:

=AVERAGE(IF(AND(Results!AF73:Results!AF65000>0,Results!AF73:Results!AF65000<100),Results!AF73:Results!AF65000))

This will work fine and will give me the average of values greater than
0:

=AVERAGE(IF(Results!AF73:Results!AF65000>0,Results!AF73:Results!AF65000))

And this works fine and gives the average of values less than 100:

=AVERAGE(IF(Results!AF73:Results!AF65000<100,Results!AF73:Results!AF65000))

But when I introduce the AND to get the average of values between 0 and
100 it just gives me 0. Any idea what’s going wrong?

Sorry to keep asking things. After my last stupid question I’ve looked
over this carefully to make sure it’s not just a stupid mistake like not
pressing Ctrl+Shift+Enter but I can’t work out what’s wrong.

Thanks again.
 
Try...

=AVERAGE(IF((Results!AF73:AF65000>0)*(Results!AF73:AF65000<100),Results!AF73:AF65000))

...confirmed with CONTROL+SHIFT+ENTER.

By the way, feel free to ask as many questions as you'd like. There'
no limit, and there will always be someone happy to help. :)

Hope this helps!
 
Thanks a lot, that's worked great.

I'll definately be using Excel a lot more so I'll have to get a boo
and learn how to use it properly
 
As Domenic said, ask away. Getting a book is also a good idea, and
personally I'd look at any of John Walkenbach's Excel Bible Series as a good
intro text that can also take you a lot further when you are comfortable
with the basics.

Also, keep doing exactly as you are doing. You were given formulas that you
tried, edited to suit your data, and then posted them back with the results
of what you did or didn't get. That makes it so much easier for people to
help you, and will usually buy you more of an interest from the folks in
here.
 
Thanks a lot for the book recommendation. I was looking at the Exce
books on Amazon today but there are so many it’s hard to know which t
go for. I’ve ordered the Excel 2003 Bible as you suggested and wil
have a good read of that when it arrives.

After that I might get a VBA book. I’ve been using a few macros bu
I’ve mostly got them off the internet and changed them a bit to suit m
purpose, so it will be good to be able to write my own
 
After that I might get a VBA book.

You just did :-)

John's book covers that too. Note the table of contents below from Part VI
onwards of the book you just ordered:-

Table of Contents

Preface.
Part I: Getting Started with Excel.
Chapter 1: Introducing Excel.
Chapter 2: Entering and Editing Worksheet Data.
Chapter 3: Essential Worksheet Operations.
Chapter 4: Working with Cells and Ranges.
Chapter 5: Worksheet Formatting.
Chapter 6: Understanding Files and Templates.
Chapter 7: Printing Your Work.
Part II: Working with Formulas and Functions.
Chapter 8: Introducing Formulas and Functions.
Chapter 9: Creating Formulas That Manipulate Text.
Chapter 10: Working with Dates and Times.
Chapter 11: Creating Formulas That Count and Sum.
Chapter 12: Creating Formulas That Look Up Values.
Chapter 13: Creating Formulas for Financial Applications.
Chapter 14: Introducing Array Formulas.
Chapter 15: Performing Magic with Array Formulas.
Part III: Creating Charts and Graphics.
Chapter 16: Getting Started Making Charts.
Chapter 17: Learning Advanced Charting.
Chapter 18: Enhancing Your Work with Pictures and Drawings.
Part IV: Analyzing Data with Excel.
Chapter 19: Working with Lists.
Chapter 20: Using External Database Files.
Chapter 21: Analyzing Data with Pivot Tables.
Chapter 22: Performing Spreadsheet What-If Analysis.
Chapter 23: Analyzing Data Using Goal Seek and Solver.
Chapter 24: Analyzing Data with the Analysis ToolPak.
Part V: Using Advanced Excel Features.
Chapter 25: Using Custom Number Formats.
Chapter 26: Customizing Toolbars and Menus.
Chapter 27: Using Conditional Formatting and Data Validation.
Chapter 28: Creating and Using Worksheet Outlines.
Chapter 29: Linking and Consolidating Worksheets.
Chapter 30: Excel and the Internet.
Chapter 31: Sharing Data with Other Applications.
Chapter 32: Using Excel in a Workgroup.
Chapter 33: Making Your Worksheets Error-Free.
Part VI: Programming Excel with VBA.
Chapter 34: Introducing Visual Basic for Applications.
Chapter 35: Creating Custom Worksheet Functions.
Chapter 36: Creating UserForms.
Chapter 37: Using UserForm Controls in a Worksheet.
Chapter 38: Working with Excel Events.
Chapter 39: VBA Examples.
Chapter 40: Creating Custom Excel Add-Ins.
Part VII: Appendixes.
Appendix A: Worksheet Function Reference.
Appendix B: What's on the CD-ROM.
Appendix C: Just for Fun.
Appendix D: Additional Excel Resources.
Appendix E: Excel Shortcut Keys.
Index.
 
Back
Top