Lookup & Match

**hayhursm** · 04-07-2009, 09:40 AM

I need to sum and report back to another spreadsheet the Total Transaction Amount in column N as it corresponds to each Account in column M but only when all of these correspond to a Department that I specify in column L. So basically the hierarchy goes
Department
Account
Total Transaction Amount (which needs to be summed up)

I have a seperate spreadsheet for each Department that lists every Account and I need the Total Transaction Amount to be summed up next to each Account from this raw data. Any help is greatly appreciated!

Also, I would like to avoid using the Autofilter function because it would be time consuming with the amount of Accounts and Departments.

**Shijesh Kumar** · 04-07-2009, 09:43 AM

Use pivot table......

**NBVC** · 04-07-2009, 09:46 AM

So if your Department ID is shown in say, A1, and your Accounts are listed starting in A2, you would need something like

=Sumproduct(--(Data!$L$2:$L$1000=$A$1),--(Data!$M$2:$M$1000=$A2),Data!$N$2:$N$1000)

adjust ranges to suit and copy down

**Shijesh Kumar** · 04-07-2009, 10:01 AM

Hi NBVC,

I have one question. I have seen in this and other forums, there are many cases where we can avoid array formula or sumproduct by creating a pivot table...but still most of the answer which are given is using array or sum product forumla..

Is there any particular reason for this like performance issue etc.

Thanks in advance for clarifying my question.

Regards,

**NBVC** · 04-07-2009, 10:04 AM

Pivot Tables are far more efficient when they can/are used versus array formulas... but not always what the OP wants... based on the Autofilter comments above...I think the OP would rather a formula solution...

I like formulas... so I usually suggest formulas...but if you/OP want to use Pivot Tables.

**hayhursm** · 04-07-2009, 10:11 AM

Thank you, I don't know why I didn't see it that way but the Pivot Table worked fine. Now I would like to graph my data but I only want to graph the Accounts & Totals that are greater than $0.00 because there are too many accounts to graph the $0.00. Is it possible to do this without going through each one?

**Shijesh Kumar** · 04-07-2009, 10:36 AM

Originally Posted by NBVC

Pivot Tables are far more efficient when they can/are used versus array formulas... but not always what the OP wants... based on the Autofilter comments above...I think the OP would rather a formula solution...

I like formulas... so I usually suggest formulas...but if you/OP want to use Pivot Tables.

Thanks for clarifying it. much appreciated.

Regards,
Shijesh