Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Function Needed
Hi
I have three sheets. Clients Name, Monday and Monday Summary. The clients name sheet is linked directly to a database and extracts Name, address and other information. On the Monday Sheet i have designed a run sheet where the user types in the client ID and with the help of Vlookup function it brings in all the cleints details that are required. The last column on the Sheet Is activity type. A category that describes what type of trip the client had. On the Monday Summary is a list of all the clients id (Approx 100) again with the use of vlookup brings there names into the sheet. What i want to sum up is the amount of trips (Monday Sheet - Column M) based on the following criteria, the Client id on the Summary sheet (A5) can be found on the Monday Sheet (Column B) and the Activty (D5) on the Summary sheet also matches activty on Monday Sheet (Column L) Hope this makes sense to someone and that they can help me out. Thanks Andrew |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Function Needed
Here's one crack at this ..
In sheet: Monday Summary, Put in E5, copy down: =SUMPRODUCT((Monday!$B$2:$B$100=A5)*(Monday!$L$2:$ L$100=D5),Monday!$M$2:$M$100) Adapt the ranges to suit the extent of data in sheet: Monday The ranges have to be identical in size, and we can't use entire col refs, eg: B:B, M:M in SUMPRODUCT -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Andrew C" wrote: Hi I have three sheets. Clients Name, Monday and Monday Summary. The clients name sheet is linked directly to a database and extracts Name, address and other information. On the Monday Sheet i have designed a run sheet where the user types in the client ID and with the help of Vlookup function it brings in all the cleints details that are required. The last column on the Sheet Is activity type. A category that describes what type of trip the client had. On the Monday Summary is a list of all the clients id (Approx 100) again with the use of vlookup brings there names into the sheet. What i want to sum up is the amount of trips (Monday Sheet - Column M) based on the following criteria, the Client id on the Summary sheet (A5) can be found on the Monday Sheet (Column B) and the Activty (D5) on the Summary sheet also matches activty on Monday Sheet (Column L) Hope this makes sense to someone and that they can help me out. Thanks Andrew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Function Needed! Please Help! | Excel Worksheet Functions | |||
Help needed with function | Excel Worksheet Functions | |||
Desperate Help needed with a function. | Excel Worksheet Functions | |||
IF function help needed | Excel Worksheet Functions |