Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    countif with 2 criteria (2003)

    Is there any way to formulate what I have done by hand in the sample attachment? On the data tab I have a list of names and what type of sale each line was (they get entered by date the sale was made). On the results tab, it lists each name and counts for each type of sale (summary). Any way to do this with a formula?
    Attached Files Attached Files

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

    Re: countif with 2 criteria (2003)

    A pivot table (Data | Pivot Table and Pivot Chart Report) is a good way to do this.
    Formulas with SUMPRODUCT are another way.
    See attached workbook.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: countif with 2 criteria (2003)

    Apologies in advance for not being able to read the 03 attachment - here in the sticks I'm celebrating 10 years of office 97, still going strong (ok, maybe not so strong).
    But, in answer to the post, you could always use an array formula such as:
    sum(if(a1:a100="ford",if(b1:b100="blue",1,0),0))
    and use ctrl+shift+enter, then you'll get a count of all blue fords from columns A and B

    Sorry if it's off topic from the content of the attachment.

    alan
    cheshire

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

    Re: countif with 2 criteria (2003)

    I think you should be able to open the attachments in this thread - the file format for Excel 2003 is the same as that for Excel 97.

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: countif with 2 criteria (2003)

    Wow, haven't had much (none actually) encounters with 2003 but again you're spot on Hans.
    Attached file has array formulas in results table.

    Cheers
    Alan
    Cheshire
    Attached Files Attached Files

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

    Re: countif with 2 criteria (2003)

    Another possible array formula is (here for Jack / Corporate):

    =SUM(($A$2:$A$11=$D11)*($B$2:$B$11=E$10))

  7. #7
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: countif with 2 criteria (2003)

    From little acorns mighty oaks et cetera.
    It never ceases to amaze me that every time I come through the lounge I learn a little something, and that formula kind of switches the light in another direction.

    gratefully
    Alan
    Cheshire

  8. #8
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif with 2 criteria (2003)

    This works great!! I also have some columns that have dollar figures in them. Example: I want to sum column C based on criteria being met in column a and column b. The sumproduct works great to count items, but any ideas how to sum them?

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

    Re: countif with 2 criteria (2003)

    You can simply add column C to the formula, e.g.

    =SUMPRODUCT((Data!$A$2:$A$11=Results!$A4)*(Data!$B $2:$B$11=Results!B$3)*Data!$C$2:$C$11)

    See attached workbook (I modified the pivot table too.)
    Attached Files Attached Files

  10. #10
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif with 2 criteria (2003)

    This worked, but seems to be limited to the number of records that can be used. Here is what I used and got the #Value error.

    =SUMPRODUCT((Data!$A$4:$A$7000=Results!$A4)*(Data! $B$4:$B$7000=Results!B$3)*Data!$C$4:$C$7000)

    I reduced the 7000's to 50's and it worked fine. Any thoughts??

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

    Re: countif with 2 criteria (2003)

    The formula should work with large ranges too. Make sure that there are no text entries in C4:C7000 - not even entries consisting of spaces only, or formulas resulting in "".

  12. #12
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif with 2 criteria (2003)

    As always, you hit the nail right on the head. It was a calculated field that displayed "" until a record was entered on that line to calculate an acutal dollar figure. I simply changed "" to a 0 in my formula. All good!!

    Thanks for the help!

Posting Permissions

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