Formula to sum across multiple worksheets but in different rows. Please help thank you!

[dmejia174]

Hi There!

I have 3 sheets. 1st tab is my consolidated tab of all General Ledger expenses on Column A. Columns C-N are the months Jan - Dec.

I am trying to create a formula on C19, that will sum all dollar amounts for the specific general ledger expense and for the specific month.

So for C19, it is 5010-0022 Legal Expense. What formula can be entered that will sum all the expenses across the 2 other sheets that may have the same expense?

[JohnTopley]

Please Login or Register  to view this content.

Remove leading blanks from column A in "Consolidated"

[AliGW]

Administrative Note:

Is your forum profile up-to-date and showing ONLY the oldest Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The four most recent Excel products are Excel 2019, Excel 2021, Excel 2024 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

[dmejia174]

Thank you John! I tried entering the formula, but it didnt give me a result. How can your formula also include the month in the criteria as well? So for example Legal expense is in both HR sheet and Asset Management Sheet and totals to 145K for the full year.

[JohnTopley]

Updated

Please Login or Register  to view this content.

OR

Please Login or Register  to view this content.

Copy across to "Dec"

[noorsadhik]

=SUMPRODUCT(('2200 HR'!$A$7:$A$29=Consolidated!$A11)*('2200 HR'!$R$6:$AC$6=Consolidated!C$9)*('2200 HR'!$R$7:$AC$29))+SUMPRODUCT(('3000 Asset Management'!$A$7:$A$26=Consolidated!$A11)*('3000 Asset Management'!$B$6:$M$6=Consolidated!C$9)*('3000 Asset Management'!$B$7:$M$26)

[dmejia174]

Thank you it worked! One last question if i may, Lets say, I have many more sheets to do this. There is about 10 more sheets, but i didnt enter it for sake of keeping it rather simple. Do i just keep adding on to the formula that you created?

[AliGW]

Which of your helpers are you talking to? Please mention their user name. Thanks.

[dmejia174]

Either one. Thanks

[JohnTopley]

Ideally, you need to get your data in the same ranges in ALL sheets so a generic formula can be provided.

You can simply add to the formula I provided but it will be rather "messy" with 10+ sheets!