How can I sort data without affecting formula?

[Guest]

I have two worksheets. On one (called ‘clients’) I have a list of clients’
names (column A) and for each client a number of other columns of data,
including one for sales value (column B). The data is not sorted at this
stage but I wish to be able to sort it by various columns.
On the other worksheet (called ‘media’) I wish to group the clients by
advertising medium and produce a sales total for all the clients contributing
to each sales medium. For example, clients D, B and A were achieved through
TV advertising and have a combined sales total of $100, while clients E and C
were from radio advertising and have combined sales of $150. My formula (on
the media worksheet) for the ‘TV’ row is a simple
=’clients’!B1+’clients’!B2+’clients’!B3 (D, B, and A are on rows 1, 2 and 3
respectively)

My problem is that when I sort the table in the clients worksheet (which is
originally in the order of D, B, A, E, C) on column A, the formula on the
media worksheet stays the same and now gives ‘TV’ the total for clients A, B,
and C instead of D, B and A. If I don’t sort the data but I add rows on the
client worksheet the column/row references in the formula change so it’s
still adding the correct figures, but when I sort the data the references are
not updated.

I hope someone can give me some clues as to how I can solve this problem.

 

[Guest]

Just use SUMIF.
If your clients sheet has clients in column A, sales data in column B, and
referral source in column C, and your media sheet has your advertising media
in column A and your totals in column B, then here's your formula.
=SUMIF(Clients!C:C,A2,Clients!B:B)
This tells the media sheet to look in column C of the clients sheet for the
value in A2 (TV, radio, etc.) and every time it finds a match, sum up the
amounts in column B just for those matches.
- KC

 

[Guest]

Thank you so much KC! That's works brilliantly! I spent hours trying to work
it out and you came back with an answer in minutes. I've never tried posting
problems online before, but this is fantastic. Many thanks!
Vicki

 

[Guest]

Hey, that's what geeks do. Sit at home at 10pm and answer Excel questions in
online forums just for the fun of it. <BG>
(that's Big Grin)

Glad I could help, and thanks for the feedback.
- KC