Results 1 to 5 of 5

Thread: COUNTIFS with OR

  1. #1
    Board Regular
    Join Date
    Jan 2013
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default COUNTIFS with OR

    Here's my formula right now:
    =COUNTIFS(Q$2:Q$1127,$B1136,F$2:F$1127,"Y",$A$2:$A$1127,"Provider")

    I want to COUNTIF $A$2:$A$1127 contains "Vendor" OR "Investor" along with the conditions for ranges in Q and F.

    I tried =COUNTIFS(Q$2:Q$1127,$B1140,D$2:D$1127,"Y",OR($A$2:$A$1127="Vendor",$A$2:$A$1127="Investor")), but that left me with Excel thinking the whole OR function was only my criteria range so that didn't work.


    Edited to add, then I need to do the same thing with SUMIFS. Right now, looks like =SUMIFS(R$2:R$1127,Q$2:Q$1127,$B1136,F$2:F$1127,"Y",$A$2:$A$1127,"Provider")
    Last edited by megera716; Aug 27th, 2019 at 03:36 PM.

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

    Default Re: COUNTIFS with OR

    Try:
    Code:
    =COUNTIFS(Q$2:Q$1127,$B1136,F$2:F$1127,"Y",$A$2:$A$1127,"Provider") + COUNTIFS(Q$2:Q$1127,$B1136,F$2:F$1127,"Y",$A$2:$A$1127,"Investor")
    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
    Board Regular
    Join Date
    Jan 2013
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS with OR

    D'oh, of course!! I should have known that. Thanks! (I assume the same thing will work for the SUMIFS).

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

    Default Re: COUNTIFS with OR

    Thanks! (I assume the same thing will work for the SUMIFS).
    You are welcome.
    Yes, the same logic should work for SUMIFS.
    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!"

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,194
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: COUNTIFS with OR

    It can also be:

    =SUM(COUNTIFS(Q$2:Q$1127,$B1136,F$2:F$1127,"Y",$A$2:$A$1127,{"Provider","Investor"}))
    Regards Dante Amor

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
  •