have some problem with database

B

baldamenti

Hello

Have this problem that I'm trying to solve. Have just discovered how
great Excel is, so I apologise if my question is noobie.

Well this is it:

In my daily work I have to fill in an excel worksheet that contains
following columns
-Date
-Customer name
-Customer number
-Product name
-Cause
-Total amount
-Signature
-Salesman

In the end of every week i have to sumarize everything into an report
and I would like to make it more automatic.

Have been making this "summarize" for a while and i have one problem.

Well my problem is that I would like to know which customers have the
biggest amounts. So I want to create formula of some kind that first
takes a look at causes, takes ones that i asign it to then takes a look
at amounts of those chosen ones and takes a biggest one. Third step is
that returns customers name that has biggest amount and is of right
cause.

It's important that it doesn't take just any big amount or cause, but
the ones in the same row.

Hope that you understand my problem, if not don't hesitate to ask
further questions...

i have been thinking of combining IF and MAX, but don't know how...

THANKS IN ADVANCE

Regards,
Baldamenti
 
V

vezerid

Assuming your columns start from A:A (thus Cause is E:E and Total is
F:F). Further assuming that you have 100 rows, thus your table data are
in A2:H101:

Highest total for a given cause:
=MAX($F$2:$F$101*IF($E$2:$E$101="MyCause", 1, 0))
To be array entered (Shift+Ctrl+Enter)

Suppose the total is in cell K2. Then, to find the customer with this
total,
=INDEX($C$2:$C$101,MATCH(K2,$F$2:$F$101,0))

HTH
Kostis Vezerides
 
Top