Summing - Different "Party Relationships"

[dfig102]

My business is based on receiving orders that are referred out from various
other shops that are similar to mine. These referrals are initially
generated from various clients.

I am creating a production report in order to identify how many orders I am
receiving from a specific "party relationship".

Example, last month I received:

Original Order From Referal Received From
Client A Shop A
Client B Shop A
Client B Shop A
Client C Shop B
Client C Shop C
Client D Shop D
Client E Shop D

So how many orders did I receive from Shop A that were diected from
Client(s) A, B, C, D, E, etc.?

Appreciate any help on this as I have been scrambling to come up with
something!

Thank you.

 

[Dave Peterson]

It sounds like a perfect time to learn about pivottables.

Select the data (include the single row of headers)
Data|Pivottable (in xl2003 menus)
Follow the wizard until you get to a dialog with a layout button on it.
Click that layout button

Drag the header for either the Original field or the referal field to the row
field.
Drag the other header to the column field.
Drag the same (other) header to the data field.

Finish up that wizard.

If you have more than 256 values of either field, then make that the row field.

========

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[Don Guillett]

=SUMPRODUCT((b2:b22="shop a")*(a2:a22={"client a","client b","etc"}))
or all use countif or
=SUMPRODUCT((b2:b22="shop a")*1)