formula question please

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

 

[Joel.R.Hill]

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!

 

[JMB]

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.

 

[JMB]

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.

 

[Pete_UK]

I have also responded to your other post.

Pete