• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

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

Hello Experts.  I need your help modifying a previous solution.  (Ref:  http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27018532.html)

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?

Gary

BCIWSH-Deliveries-and-Lines--S-0.xls
0
garyrobbins
Asked:
garyrobbins
  • 2
  • 2
1 Solution
 
barry houdiniCommented:
Hello Gary, try these versions - for count

=SUMPRODUCT((MATCH(E8:E38&"-"&I8:I38&"-"&Y8:Y38,E8:E38&"-"&I8:I38&"-"&Y8:Y38,0)=ROW(E8:E38)-ROW(E8)+1)+0)

and then for sum....

=SUMPRODUCT((MATCH(E8:E38&"-"&I8:I38&"-"&Y8:Y38,E8:E38&"-"&I8:I38&"-"&Y8:Y38,0)=ROW(E8:E38)-ROW(E8)+1)+0,Y8:Y38)

regards, barry
0
 
garyrobbinsAuthor Commented:
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
0
 
barry houdiniCommented:
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 these.......so 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 25....so 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
0
 
garyrobbinsAuthor Commented:
Thanks, Barry.  Care to suggest a good reference book to learn more about this function?  Gary
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now