Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    countif (excel 2003)

    Could someone take a peek at this worksheet for me. I don't know, maybe I'm just overtired, but I can't get a vlookup, CountA or CountIF, or Count function to come up with what I need. I'm not even positive this is exactly the way it was in the students spreadsheet, but what I am looking for (I think) is a nested COUNTIF example based on two pieces of criteria. If I have something over here, count if, and then go count another condition in a different column based on another piece of criteria. I have tried to explain it better in the sheet if anyone has time.
    Thanks a bunch
    Attached Files Attached Files
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: countif (excel 2003)

    I don't understand the description in your workbook. Could you explain what you want and what you expect the result to be?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: countif (excel 2003)

    <P ID="edit" class=small>(Edited by Jezza on 13-Dec-07 23:42. To adjust glaring mistake in Formula)</P>Hi There

    Does this do what you want:

    =SUMPRODUCT(--(A1:A5="FRE")*(B1:B5)) + SUMPRODUCT(--(D15="HBN")*(E1:E5))
    Jerry

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif (excel 2003)

    Jezza,

    That did exactly what I wanted. I had looked up the SUMPRODUCT example in another forum question but couldn't get it to work...you most certainly did. Thank you. If she wants something different I'll be back, but this is what I got out of her question, and your answer gave me what I think she was after. Will let you know after I see her in class tomorrow.
    Thank you so much.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif (excel 2003)

    By the way...I encourage them to visit this site when they have questions like this...it shows them that Woody's Lounge is available to them 24/7, and gives them an excellent help resource after class is said and done...what would any of us do without all of you?
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: countif (excel 2003)

    Here's an alternative formula:

    =SUMIF(A1:A5,"FRE",B1:B5)+SUMIF(D16,"HBN",E1:E6)

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: countif (excel 2003)

    Hi there

    I just wanted to clarify what I did for other Loungers and your students.

    I have used the double unitary figure -- (two minus signs) to tell sumproduct to ignore errors and to act as a classic sumproduct function. An error could have been introduced in this part example

    SUMPRODUCT(--(D15="HBN")*(E1:E5))

    where criteria were not identified for the range E1:E5, we were lucky in this instance but I paced them in just in case <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif (excel 2003)

    Thank you Hans, I will give her all choices listed here and hope this works out for her...

    PS...Jezza, thank you for the clarification and explanation to your formula...I was wondering what the -- was! Had not seen before. Thank you both very much...
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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