Lookup with 2 criteria?

[KrisDHolm]

I have a data tab with 3 columns.

A B C
date account $

There are probably 20,000+ rows of data. On a seperate tab I have a
reports tab that totals out the accounts I care about with the date
across the top.

A B C D
1 8/1/09 8/2/09 8/3/09
2account x
3account y


My question is how can I write a formula that totals this out for me
automatically. Currently I sort by date and I have to do a vlookup and
only name a range that has 1 date. I think there is a better way. Help
if we can.

 

[muddan madhu]

=SUMPRODUCT((sheet1!$A$2:$A$6=B$1)*(sheet1!$B$2:$B$6=$A2)*(sheet1!$C
$2:$C$6))

 

[barry houdini]

If there could be multiple entries for an account on a single date then
try this formula in B2 copied across and down

=SUMPRODUCT((B$1=Data!$A$2:$A$20000)*($A2=Data!$B$2:$B$20000),$C$2:$C$20000)

or if there will only be one entry per account per date at most then
try

=LOOKUP(2,1/(B$1=Data!$A$2:$A$20000)/($A2=Data!$B$2:$B$20000),$C$2:$C$20000)

 

[Ashish Mathur]

Hi,

You could create pivot tables. Drag Date to the column area, Account to
the row area and Amount to the data area.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com