excel formula question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mona - 22 Aug 2006 01:16 GMT

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

Reply to this Message

Pete_UK - 22 Aug 2006 02:03 GMT

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

> I am having trouble coding the correct formula. Here is an example of data
> in worksheet1
[quoted text clipped - 23 lines]
> Thank you .
> ....

Reply to this Message

tukjodjam - 22 Aug 2006 05:49 GMT

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

-------------------------------------------

"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
[quoted text clipped - 54 lines]
> > Thank you .
> > ....

Reply to this Message

JMB - 22 Aug 2006 02:10 GMT

see response at your other post.

> I am having trouble coding the correct formula. Here is an example of data
> in worksheet1
[quoted text clipped - 23 lines]
> Thank you .
> ....

Reply to this Message