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

Posted on 2011-05-12
Last Modified: 2012-05-11
Hello Experts.  I need your help modifying a previous solution.  (Ref:

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).  You already helped me develop a formula to count the quantity of unique entries within each delivery (see I42).  

Now I need to add criteria to that formula to also count when the Quantity (Col Y) changes within an Item Number.

Can you give me a hand?


Question by:garyrobbins
    LVL 50

    Accepted Solution

    Hello Gary, try these versions - for count


    and then for sum....


    regards, barry

    Author Closing Comment

    Barry -- you are a wizard!

    Could you give me a short description of how this count formula works?  Mainly the MATCH function inside...

    Thanks, Gary
    LVL 50

    Expert Comment

    by:barry houdini
    What the MATCH function is doing is matching each concatenation of the relevant cells in each row against a range of ALL such concatenations. Because MATCH finds the position of the first match then that number will always be the same for each similar concatenation, e.g. if you have "x", "y", "z" in row 8 and also in row 15 and 25 then the MATCH function will return 1 (because 8 is the first match and that's row 1 of the range) for all of we compare the match function result against the relative row number in the range ( provided by ROW( rather than just count.E8:E38)-ROW(E8)+1) so this is 1 in row 8, 8 in row 15 and 18 in row for my example there's only a match for row 8.......i.e. only the first row of identical combinations is counted.

    For the second formula a sum range is added to sum column Y rather than just count

    does that make sense, it's quite a complex concept....

    regards, barry

    Author Comment

    Thanks, Barry.  Care to suggest a good reference book to learn more about this function?  Gary

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    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…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    760 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

    7 Experts available now in Live!

    Get 1:1 Help Now