If you are likely to want a total for 2dr cars, 4dr cars, 2dr vans and
4dr vans, I would suggest that on Sheet2 you include these parameters
in the headings, i.e. put "car" in B1 and C1, "van" in D1 and E1, "2dr"
in B2 and D2, and "4dr" in C2 and E2, and thus have your dates starting
in A3. Enter this array* formula in B3:

=SUM(IF((Sheet1!$A$2:$A$100=$A3)*(Sheet1!$B$2:$B$1 00=B$1)*(Sheet1!$C$2:$C$100=B$2),Sheet1!$E$2:$E$10 0,0))

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you need to use CTRL-SHIFT-ENTER (CSE) instead of
just ENTER. If you do this correctly then Excel will wrap curly braces
{ } around the formula - you should not type these yourself.

Adjust the ranges to suit your data - I have assumed 100 rows. The
formula can be copied across and down to suit the number of dates you
have in Sheet2.

You can make the formula more readable (and shorter) by using named
ranges. In Sheet1 highlight all the data including the headings and
Insert | Name | Create. Ensure that only Top Row is ticked in the
Create Name box, then click okay - this will have created named ranges
using the headings as names. The formula can then be written:

=SUM(IF((DATE=$A3)*(value1=B$1)*(value2=B$2),Total 2,0))

Again, CSE to commit the formula, then copy across and down.

Hope this helps.

Pete

Mona wrote:
I am having trouble coding the correct formula. Here is an example of data
in worksheet1

A1 B1 C1 D1 E1
DATE value1 value2 Total1 Total2
03/01/2006 car 4dr 300 200
03/01/2006 car 2dr 200 100
03/01/2006 van 4dr 600 200
03/01/2006 van 2dr 300 100
03/02/2006 car 4dr 300 200
03/02/2006 car 2dr 200 100
03/02/2006 van 4dr 600 200
03/02/2006 van 2dr 300 100

In worksheet2 I have:
A1 B1
Date Car-2dr-total2
03/01/2006 ?
03/02/2006 ?
03/03/2006 ?

I need assistance is creating a formula in worksheet2 (b2) that will give me
date =03/01/2006 and value1=car and vaule2=4dr equals total2. I hope this
makes sense.

Thank you .
....