Results 1 to 4 of 4

Thread: Count IF (03)

  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count IF (03)

    I have two columns, the first being a "name" and the second an "amount" I would like to count the cells in a range whose "name" is equal to "Hans" and whose "amount" is <> 0

    For example:
    =CountIf((D545="Hans")*(E5:E45,<>0))

    I have tried array formulas and the like but can not resolve this one. It's been a long day....

    John

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

    Re: Count IF (03)

    If you need more than one condition, you cannot use COUNTIF. Instead, use either

    =SUMPRODUCT((D545="Hans")*(E5:E45<>0))

    as a normal formula, or

    =SUM((D545="Hans")*(E5:E45<>0))

    as an array formula (confirm with Shift+Ctrl+Enter).

  3. #3
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count IF (03)

    G'Day Y'all,

    I have a related question:
    I need to count the number of times where in two different columns when any text is in column A and the corresponding cell in column B is blank. I need to ignore occurrences of column A blank and column B blank; and ignore occurrences of column A contains any text and column B any contains text .

    Best regards,
    Rich

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Count IF (03)

    <P ID="edit" class=small>(Edited by sdckapr on 27-May-06 07:38. Added PS)</P>How about something like (change range as desired)
    <pre>=SUMPRODUCT(ISTEXT(A1:A100)*ISBLANK(B1:B100)) </pre>


    It counts (as requested)
    Text in A and Blank A

    It ignores (as requested)
    Blank in A and Blank in B
    Text in A and Text in B

    It also ignores (not listed either way):
    Blank in A and Text in B

    Steve
    PS: You can use, if you want Blank in A and Text in B to be counted:
    =SUMPRODUCT(ISTEXT(A1:A100)*ISBLANK(B1:B100))+SUMP RODUCT(ISBLANK(A1:A100)*ISTEXT(B1:B100))

Posting Permissions

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