+ Reply to Thread
Results 1 to 2 of 2

Football Coach needs help

  1. #1
    Registered User
    Join Date
    02-08-2006
    Posts
    1

    Football Coach needs help

    I am trying to use Excel to calculate tendencies over the course of a football season. I can get the numbers I want through using the Filters, but I was hoping that I could build some formulas and the numbers would run for me.

    Example: How many 1st Down plays were there?
    Answer: COUNTIF statement in the Down column

    Example: How many yards were gained on 1st down?
    Answer: SUMIF statement referencing the Down column but summing the Yards column

    The same holds true for how many passes and runs, and yards for each.

    Here's the rub, how can you compute a total number with 2 separate criteria:

    How many run plays were there on 1st Down?

    I have looked at the pivot tables too, and I think there may be some answers there, but I am not real knowledgable in that area either.

    Thanks for any help.

    -Mike

  2. #2
    Bernie Deitrick
    Guest

    Re: Football Coach needs help

    Mike,

    > How many run plays were there on 1st Down?


    Formula:

    =SUMPRODUCT((rangewithdowns=1)*(rangewithplaytype="RUN"))

    And you're right: Pivot tables would definitely be the way to go. Then you could select any criteria
    combination (including others that you did not mention - quarterback sneaks on second downs in the
    third quarter with the wind blowing from the west....) and show the count without developing a
    custom formula.

    If you want to try the pivot table apporach, email me a worksheet, and I'll set it up.

    HTH,
    Bernie
    MS Excel MVP


    "aldy67" <aldy67.22x90z_1139420704.8552@excelforum-nospam.com> wrote in message
    news:aldy67.22x90z_1139420704.8552@excelforum-nospam.com...
    >
    > I am trying to use Excel to calculate tendencies over the course of a
    > football season. I can get the numbers I want through using the
    > Filters, but I was hoping that I could build some formulas and the
    > numbers would run for me.
    >
    > Example: How many 1st Down plays were there?
    > Answer: COUNTIF statement in the Down column
    >
    > Example: How many yards were gained on 1st down?
    > Answer: SUMIF statement referencing the Down column but summing the
    > Yards column
    >
    > The same holds true for how many passes and runs, and yards for each.
    >
    > Here's the rub, how can you compute a total number with 2 separate
    > criteria:
    >
    > How many run plays were there on 1st Down?
    >
    > I have looked at the pivot tables too, and I think there may be some
    > answers there, but I am not real knowledgable in that area either.
    >
    > Thanks for any help.
    >
    > -Mike
    >
    >
    > --
    > aldy67
    > ------------------------------------------------------------------------
    > aldy67's Profile: http://www.excelforum.com/member.php...o&userid=31332
    > View this thread: http://www.excelforum.com/showthread...hreadid=510139
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1