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

Brass Contributor

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. 

Thank you in advance

 

Tejas Shah

 

8 Replies

@Tejas_shah 

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.

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

@Tejas_shah 

Sorry, but i don't understand.

@Riny_van_Eekelen 

 

Screenshot 2024-05-30 095050.png

 

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

@Tejas_shah 

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?



@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.

Hope i clarified your doubt.
Wating for the solution.
Thank for the response.
Tejas shah


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

Riny_van_Eekelen_0-1717059284822.png

....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.

 

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