Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    countif with two criteria? (2000 sr1)

    I need to count the entries in column b that are over 0 (not negatives or 0) and where the data in the same row in column a is equal to "E".
    For example;
    A B
    a -4
    b 7
    e 6
    e -9
    a 7
    Would give me the answer 6. I have tried a host of combinations with Countif, If, and the like array entered and I cannot get around this problem. Help!
    Neil Eustice
    Woody Worshipper

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: countif with two criteria? (2000 sr1)

    Neil, if you really meant count in the Excel sense then the answer is 1 and the formula is {=COUNT(IF((B1:B5>0)*(A1:A5="e"),1,""))}. However, if you meant sum, then the answer is 6 and the formula is {=SUM(IF((B1:B5>0)*(A1:A5="e"),B1:B5,""))}
    In both cases, the {} are entered by Excel, not you, because these are array formulas and are entered with <Ctrl><Shift><Enter>. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif with two criteria? (2000 sr1)

    Appologies, I did mean count, and typing it in a hurry put 6 instead of 1. Thanks anyway.
    Neil Eustice
    Woody Worshipper

  4. #4
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif with two criteria? (2000 sr1)

    Another problem for you. What if I wanted to sumproduct on columns b and c but only where column A was = "E"?
    Neil Eustice
    Woody Worshipper

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: countif with two criteria? (2000 sr1)

    Hi Neil,
    Try something like:
    =sum((A1:A5="E")*(B1:B5)*(C1:C5))
    array-entered (adjust ranges as necessary).
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif with two criteria? (2000 sr1)

    This includes cells where the data in C is a negative. Just to make things harder, I only want to sumproduct where data in column A is = "E" and data in column c is >0.

    Thanks for the help.
    Neil Eustice
    Woody Worshipper

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: countif with two criteria? (2000 sr1)

    Neil, in your original example there was no column C so I'm a tad confused, but does

    {=SUM((column_A_range>0)*(column_B_range="e")*(col umn_A_range))}

    array entered, do what you want or get you in the right direction?
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: countif with two criteria? (2000 sr1)

    Oh OK. Then either:
    =sum((A1:A5="E")*(B1:B5)*(C1:C5>0)*(C1:C5))
    or
    =sum((A1:A5="E")*(B1:B5)*if(C1:C5>0,C1:C5,0))
    both array-entered should do it.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: countif with two criteria? (2000 sr1)

    <img src=/w3timages/blackline.gif width=33% height=2>
    > sumproduct on columns b and c but only where column A was = "E"?
    <img src=/w3timages/blackline.gif width=33% height=2>

    Assuming you added a column of numbers in C1:C5:<pre>{=SUMPRODUCT(IF(A1:A5="e",B1:B5,0),IF(A 1:A5="e",C1:C5,0))}</pre>

    entered w/o the braces as an array formula.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif with two criteria? (2000 sr1)

    Thanks
    Neil Eustice
    Woody Worshipper

  11. #11
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif with two criteria? (2000 sr1)

    {=SUMPRODUCT(IF(A1:A7="e",B1:B7,0),IF(B1:B7>0,C1:C 7,0))} array entered is the idea I was after. This anwser came from SammyB and I just adjusted it to suit my needs. Sorry about any confusion but I was amending what I wanted to do on the fly.

    Cheers
    Neil Eustice
    Woody Worshipper

  12. #12
    New Lounger
    Join Date
    Jan 2001
    Location
    Nova Scotia
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif with two criteria? (2000 sr1)

    I won't crticize the solutions presented which are probably correct. However, I personally find them rather complicated in spite of the fact that I have been using Excel for too many years to remember. I often wonder why people don't create an extra column in this case with the appropriate logic to calculate the impact on one cell. Then you can copy that logic to all the cells in the column and perhaps sum the total at the bottom. Perhaps it will create a larger file but the logic is eminently easier to follow and probably correct. In addition, the process is usually easier to scale up to more difficult problems.

  13. #13
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: countif with two criteria? (2000 sr1)

    Amen, I wrote them & I hate them. I would aviod an array formula at all costs. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: countif with two criteria? (2000 sr1)

    To each his own. We are all guilty of excessive spreadsheet elegance at some time.

    On one hand I prefer the simplest solution (Occam's razor) but I have a strong preference for single cell formulas for layout reasons. I get too much multicolumn true mean calculations from people who don't use =SUMPRODUCT as it is; using extra cells when it is isn't necessary can obscure the intent and focus of the analysis.

    But I also think you should consider one other reason to use array formulas in the context used here; they are much easier than setting up an equivalent =DSUM(,,) formula, which is the only other way to do complex criteria formulas.
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: countif with two criteria? (2000 sr1)

    I partially agree with you but it is not always possible to put in that extra column. It depends what is going on with the rest of the data and other automated tasks already set in motion. However, a good point and certainly one worth thinking about when planning new spreadsheets.
    Neil Eustice
    Woody Worshipper

Posting Permissions

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