asked on

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

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

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

and for I73

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

regards, barry

garyrobbins

ASKER

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

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

garyrobbins

ASKER

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