have some problem with database
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 -- baldamenti ------------------------------------------------------------------------ baldamenti's Profile: http://www.excelforum.com/member.php...o&userid=28071 View this thread: http://www.excelforum.com/showthread...hreadid=475814 |
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 |
All times are GMT +1. The time now is 02:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com