formula question please
 

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

formula question please
 

Will you be able to have a stored hidden column? If so I recommend
having the following formula in Column F of Worksheet1 be the
following:

=A3&B3&C3

And for Worksheet2 have this for Cell B2:

=SUMIF(Sheet1!F3:F10,"03/01/2006car4dr",Sheet1!E3:E10)

Hope this helps!

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

formula question please
 

One other thing - I was assuming in my last post that if there are multiple
matches you want the total for all matches. If not, you could try an array
formula to return the first match:

=INDEX(Sheet1!$E$2:$E$10, MATCH(A2&"car4dr",
Sheet1!$A$2:$A$10&Sheet1!$B$2:$B$10&Sheet1!$C$2:$C $10, 0))

confirmed with Control+Shift+Enter.


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

formula question please
 

Try:
=SUMPRODUCT(--(Sheet1!$A$2:$A$10=A2), --(Sheet1!$B$2:$B$10="car"),
--(Sheet1!$C$2:$C$10="4dr"), Sheet1!$E$2:$E$10)

where A2 refers to the date on sheet2 you are looking for. also, you can
replace "car" and "4dr" with cell references. change Sheet1 range references
as needed, however, sumproduct cannot accomodate an entire column as an
argument.



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

formula question please
 

I have also responded to your other post.

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