Using OR in a COUNTIFS statement

Posted on 2012-08-23
Last Modified: 2012-08-23
=COUNTIFS(Static!A2:A78438,OR(120,121),Static!B2:B78438,"NO PRICING")

This is returning 0 when there are over 1000.
Any ideas what could be wrong?

I want to count how many say NO PRICING in column B when there are either 120 or 121 in column A.

Question by:Euro5
    LVL 13

    Expert Comment

    I would say

    =COUNTIFS(Static!A2:A78438,"=120",Static!B2:B78438,"NO PRICING")+COUNTIFS(Static!A2:A78438,"=121",Static!B2:B78438,"NO PRICING")

    Being the easy solution
    LVL 50

    Accepted Solution

    You could also use this version

    =SUM(COUNTIFS(Static!A2:A78438,{120,121},Static!B2:B78438,"NO PRICING"))

    ...especially useful if there are multiple OR items....

    regards, barry
    LVL 24

    Expert Comment

    This will also work: allows for numbers between a range not just 120-121

    =COUNTIFS(Static!B2:B78438,"NO PRICING",Static!A2:A78438,">=120",Static!A2:A78438,"<=121")

    Author Closing Comment

    Thanks so much!! Perfect!

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    Many companies are making the switch from Microsoft to Google Apps ( Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now