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

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?

Gary

BCIWSH-Deliveries-and-Lines--S-0.xls
garyrobbinsAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
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 H73......you 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

=SUMPRODUCT((MATCH(E8:E69&"-"&H8:H69,E8:E69&"-"&H8:H69,0)=ROW(E8:E69)-ROW(E8)+1)+0)

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

=SUMPRODUCT((MATCH(E8:E69&"-"&H8:H69,E8:E69&"-"&H8:H69,0)=ROW(E8:E69)-ROW(E8)+1)+0,X8:X69)
0
 
barry houdiniCommented:
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

=SUMPRODUCT((H7:H68<>H8:H69)+0)

and for I73

=SUMPRODUCT((H7:H68<>H8:H69)+0,X8:X69)

regards, barry
0
 
garyrobbinsAuthor Commented:
Barry,

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??

Gary
0
 
garyrobbinsAuthor Commented:
Barry,

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.

Gary
0
All Courses

From novice to tech pro — start learning today.