Sum Function Needed

[Guest]

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

 

[Guest]

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