Counting Instances Of Item In A Column

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 near a solution.

I'll try and put them into my actual table now.

Pivot Tables are a good way to analyse this kind of data:-

http://peltiertech.com/Excel/Pivots/pivotstart.htm

but for a formula based approach, perhaps try the following:-

=SUMPRODUCT(--(C3:C30="Z"),--(B3:B30="S"))/COUNTIF(C3:C30,"Z")

where Col C holds your X and Zs, and Col B holds the Success or Fail
indicators.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Mr Milquetoast"
<[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> Mr Milquetoast
> ------------------------------------------------------------------------
> Mr Milquetoast's Profile:
http://www.excelforum.com/member.php...o&userid=23987
> View this thread: http://www.excelforum.com/showthread...hreadid=376055
>

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?

No need to array enter the SUMPRODUCT formula, a simple Enter here works
fine.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"MrShorty" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> MrShorty
> ------------------------------------------------------------------------
> MrShorty's Profile:
http://www.excelforum.com/member.php...o&userid=22181
> View this thread: http://www.excelforum.com/showthread...hreadid=376055
>

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.

Originally Posted by Mr Milquetoast

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?

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

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

Thanks again.

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.



In article
<[email protected]>,
Mr Milquetoast
<[email protected]> wrote:

> 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?

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's no limit, and there will always be someone happy to help.

Hope this helps!

Originally Posted by Mr Milquetoast

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.

Thanks a lot, that's worked great.

I'll definately be using Excel a lot more so I'll have to get a book 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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Mr Milquetoast"
<[email protected]> wrote in
message news:[email protected]...
>
> Thanks a lot, that's worked great.
>
> I'll definately be using Excel a lot more so I'll have to get a book
> and learn how to use it properly.
>
>
> --
> Mr Milquetoast
> ------------------------------------------------------------------------
> Mr Milquetoast's Profile:
http://www.excelforum.com/member.php...o&userid=23987
> View this thread: http://www.excelforum.com/showthread...hreadid=376055
>

Thanks a lot for the book recommendation. I was looking at the Excel books on Amazon today but there are so many it’s hard to know which to go for. I’ve ordered the Excel 2003 Bible as you suggested and will have a good read of that when it arrives.

After that I might get a VBA book. I’ve been using a few macros but I’ve mostly got them off the internet and changed them a bit to suit my 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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Mr Milquetoast"
<[email protected]> wrote in
message news:[email protected]...
>
> Thanks a lot for the book recommendation. I was looking at the Excel
> books on Amazon today but there are so many it's hard to know which to
> go for. I've ordered the Excel 2003 Bible as you suggested and will
> have a good read of that when it arrives.
>
> After that I might get a VBA book. I've been using a few macros but
> I've mostly got them off the internet and changed them a bit to suit my
> purpose, so it will be good to be able to write my own.
>
>
> --
> Mr Milquetoast
> ------------------------------------------------------------------------
> Mr Milquetoast's Profile:
http://www.excelforum.com/member.php...o&userid=23987
> View this thread: http://www.excelforum.com/showthread...hreadid=376055
>