# How to get the Account code from the formula refer the amount column

Brass Contributor

# How to get the Account code from the formula refer the amount column

Hi,

I have 2 sheets in workbook. 1st sheet is Summary sheet and other one is details sheet. I have attached the sample sheet with required result in highlighted cells,

1st sheet having the sample balance sheet where it summarizes the accounts amounts from the details sheet. i want the account number in 1st sheet against group of balance sheet accounts as per the amount total.

Tejas Shah

8 Replies

# Re: How to get the Account code from the formula refer the amount column

Try this:

``=SUM(MMULT(TRANSPOSE(Details!\$F\$4:\$F\$14),--(Details!\$B\$4:\$B\$14=E4:J4)))``

Entered in D4 of the attached file and copied down.

# Re: How to get the Account code from the formula refer the amount column

Thank you for the reply I want the accounts no which is highlighted in summary sheet.

# Re: How to get the Account code from the formula refer the amount column

Sorry, but i don't understand.

# Re: How to get the Account code from the formula refer the amount column

I want the for PPE which are the accounts added to for the value. i.e. 1001 - 1008 which is highlighted.

# Re: How to get the Account code from the formula refer the amount column

Still don't understand. I assumed you wanted a formula that calculated 1250 for PPE, based on the account numbers mentioned. Isn't that correct?

# Re: How to get the Account code from the formula refer the amount column

@Riny_van_Eekelen
No i want the account code which is calculated the amount 1250.
so next to ppe cell i have formula which is getting the amount from detail sheet. Ie. =SUM(Details!F4:F7,Details!F10:F11)
So i Want the corresponding account code which is in Column B in detail sheet.

So i gave the example in summry sheet which accounts total summing up to PPE.

Wating for the solution.
Thank for the response.
Tejas shah

# Re: How to get the Account code from the formula refer the amount column

@Tejas_shah If you have Excel for MS365 you could try this:

....but you have to hard code the ranges in each formula. Can't think of a way to automatically generate this. And if I may say so it's quite odd to do this. I would start by creating a reference table with at three columns. Account Number, Account Name and Report Category. Then it becomes easy to sum and list out, for instance, all PPE accounts.

# Re: How to get the Account code from the formula refer the amount column

That will not resolve my problem, I have more than 30 items in the summary sheet and i have 175 account codes.