Results 1 to 5 of 5

Thread: Multiple CountIfs
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2010
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Multiple CountIfs

    I have a large spreadsheet (to ER3500) and need to count specific columns with non-blank cells across the rows. I have tried several CountIf and CountIfs, but I can't get there. A sample spreadsheet is below. I want the total number of rows which have data in the Account, Color, and Response columns. In this example the answer would be 2 (accounts 1204 and 1206). Can you help?
    Account City Color Response
    1202 Boston Y
    1203 Red
    1204 NY Blue N
    Dallas Green
    1206 Purple Y
    LA Y
    1208 N

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,772
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Multiple CountIfs

    Assuming your example is in range A1:D8, you can do something like this:
    Code:
    =SUMPRODUCT(--(A2:A8<>""),--(C2:C8<>""),--(D2:D8<>""))
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,177
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Multiple CountIfs

    How about
    =COUNTIFS(A2:A8,"<>",C2:C8,"<>",D2:D8,"<>")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular
    Join Date
    Jun 2010
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple CountIfs

    Thank you Joe4 and Fluff! Both your solutions worked, and saved me a ton of time. Thank you both!

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,177
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Multiple CountIfs

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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