How can I use a SUM IF FREQUENCY formula with additional criteria?

Posted on 2011-05-04
Last Modified: 2012-05-11
Hello Experts.  I need your help again.

I have been using the SUM IF FREQUENCY formula with some success (see attached sample file).  I need help modifying it to take into account other criteria.

I am working with a large legacy report to extract summaries.  I have many duplicate entries.  Using the SUM IF FREQUENCY I have been able to count the quantity of unique entries (orders & deliveries – columns E & F).  Now I want to count the quantity of unique entries within each delivery (see column H).  

I also need help summing the quantity of pieces for only those unique entries (see column X).

Can you give me a hand?


Question by:garyrobbins
    LVL 50

    Expert Comment

    by:barry houdini
    Hello gary

    Shouldn't H73 be 7?

    It's not clear to me if the item numbers can repeat in different orders, if not then I think it's quite straightforward, you can just count how many times the item changes, i.e. for H73


    and for I73


    regards, barry

    Author Comment


    I'm glad you noticed my challenge...  
    Item numbers can repeat in different orders.  H73 should be 6.

    What is the <> in your formula suggestion??

    LVL 50

    Accepted Solution

    Hello Gary,

    <> is "not equal to" - that formula effectively just counts the number of times the item number changes.......

    I still think it's 7 for have highlighted 7 values in red, i.e. H8, H14, H30, H46, H52, H58, H64, aren't you counting those?

    This formula would count those 7 and would count each different delivery/item combination (even if items repeat in different deliveries. If you want order/item combinations then change Es to Fs


    similarly you can sum by adding that range to the same formula, i.e.


    Author Closing Comment


    Thanks for the fast and accurate answer.

    You are right -- 7 was the correct answer...  I realized that as I was driving home last night!  Thanks for catching it.

    I am submitting a secondary question next to identify those entries that are duplicated.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    779 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

    18 Experts available now in Live!

    Get 1:1 Help Now