Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup with 2 criteria?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup with 2 criteria?
=SUMPRODUCT((sheet1!$A$2:$A$6=B$1)*(sheet1!$B$2:$B $6=$A2)*(sheet1!$C
$2:$C$6)) On Oct 21, 12:35*am, KrisDHolm wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup with 2 criteria?
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 "KrisDHolm" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Lookup on two criteria | Excel Worksheet Functions | |||
Lookup using more than one criteria | Excel Worksheet Functions | |||
Lookup? for more than one criteria | Excel Worksheet Functions | |||
lookup with 2 criteria | Excel Discussion (Misc queries) |