Not sure which formulas to use!!!

[emmatm]

I have a trial balance on a tab of my worksheet which has the folowing columns:
Branch
Division
Division Name
Nominal Code
Nomina Name
Value
This list is nearly 15,000 rows.

I then have other tabs which have a set of financial accounts per company with the nominal code listed in column A - so for example lets say on row 7 in column A I may have code 6000 and then in cell F7 I want to return the value from my trail balance.
The trail balance for this company may have more than 1 division so I would want to say from:

Branch - look for all branch 1
Division - look for all divisions 3 & 6
Division Name - NA
Nominal Code - look for all nominal codes 6000
Nominal Name - NA
Value - return the exact value for all the combined rows.

Many thanks

 

[Somendra Misra]

Hi,

Can you just upload a small sample file with data and required output in it.

Regards,

 

[bobhc]

If you are able to turn your data table into a pivot you will be able to "pivot" the data as you need.

 

[emmatm]

I have attached just a section from my original worksheet. This shows divisions 3 & 6 only, so for example if we look at nominal code 6100 I would expect to be able to return the result of £1,747,531.29

 

[Somendra Misra]

See the file, I had applied some headers and created the PIVOT Table, will this work or you want some other output format?

Regards,

 

[emmatm]

See the file, I had applied some headers and created the PIVOT Table, will this work or you want some other output format?

Regards,

Thanks howver I didnt want to use a pivot table, I need to be able to get the figure for each nominal (using more than one division) onto another tab - I gave you an example of one nominal code earlier which is 6100 but I am trying to extract a value for every nominal code in the TB on a seperate sheet

 

[Somendra Misra]

See the file Sheet2, i had used SUMIFS with one criteria, you can use multiple criteria there.

Regards,

 

[prasaddn]

Hi
As there are only two divisions, can you put the division 6 in a separate sheet or even sorted will do. Then apply a simple VLookUp formula and add the result with F column value.

Regards,
Prasad DN

 

[emmatm]

See the file Sheet2, i had used SUMIFS with one criteria, you can use multiple criteria there.

Regards,

Thanks this is more like what I was looking for however what if the full range (I only gave you a small sample) had other divisions with the same code 6100 and i only wanted a selected few divisions.
So you have returned the value based on only the nominal code but what if I wat to break it further by division - hope that makes sense!
Can you show me how you use multiple criteria?

 

[Somendra Misra]

@emmatm

I would suggest you to upload a sample file, with the criteria set in cells as per your choice and and required output.

Regards,

 

[emmatm]

@emmatm

I would suggest you to upload a sample file, with the criteria set in cells as per your choice and and required output.

Regards,

Here is another sample file, can you give me the results on a seperate tab as you did on my previous file - I am looking for the total value of nominal code 6100 but only for divisions 3 & 6 NOT 9.

Thanks

 

[Somendra Misra]

See the file, yellow cell has formula.

Regards,