excel formula question

[Mona]

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 .
.....

 

[Pete_UK]

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$100=B$1)*(Sheet1!$C$2:$C$100=B$2),Sheet1!$E$2:$E$100,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),Total2,0))

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

Hope this helps.

Pete

 

[JMB]

see response at your other post.

 

[tukjodjam]

your requirement, you must input data 3 Column, Date-value1-value2 equal to
Total2
....
you must add new column...Generate with formular [ =B3&C3&D3 ]

Generate DATE value1 value2 Total1 Total2
38720car4dr 3/1/2006 car 4dr 300 200
38720car2dr 3/1/2006 car 2dr 200 100
38720van4dr 3/1/2006 van 4dr 600 200
38720van2dr 3/1/2006 van 2dr 300 100
38751car4dr 3/2/2006 car 4dr 300 200
38751car2dr 3/2/2006 car 2dr 200 100
38751van4dr 3/2/2006 van 4dr 600 200
38751van2dr 3/2/2006 van 2dr 300 100
38849box5dr 12/5/2006 box 5dr 400 600
38849box3dr 12/5/2006 box 3dr 200 700

and use function Vlookup(...) In worksheet2 at column Car-2dr-total2


Generate DATE value1 value2 Car-2dr-total2
38720car4dr 3/1/2006 car 4dr 200
38751van4dr 3/2/2006 van 4dr 200
38849box5dr 12/5/2006 box 5dr 600


=VLOOKUP(A11,Sheet5!A2:F10,6)
***test by***
- copy formularin column Generate,Car-2dr-total2 to next ROW
- input Data in 3 Column
- you Hide column Generate