none
Excel Formula RRS feed

  • Question

  • I am doing some bookkeeping in excel and using drop-down lists. I am trying to get account totals.

    Column D = Acct from drop-down list.  column G = Amount

    How do I get it to add only the amounts for a particular account and can I set it up so that it adds this info from more than one worksheet?  Thanks.

    • Moved by litdev Sunday, May 28, 2017 7:22 AM
    Sunday, May 28, 2017 3:04 AM

Answers

All replies

  • Hi Maggiejt.

    You can ask this question in the Microsoft Community for Office.

    Bye.


    Luigi Bruno
    MCP, MCTS, MOS, MTA

    Sunday, May 28, 2017 10:18 AM
    Moderator

  • The below steps will demonstrate one method to carry out your work. Use it on a blank Excel file, so that you understand how it works, and then use it on your main file.

    I have assumed 'Acct' to be in cells D1 to D10 and 'Amount' to be in G1 to G10, in each sheet from Sheet1 to Sheet 5.

    In column I, of Sheet1, list the items present in column D (Acct) of all the sheets. Remove duplicates.

    Select Sheet1. With Shift key pressed, select Sheet5. All sheets from Sheet1 to Sheet5 will get selected. Whatever you do on 1 sheet, will happen on all the sheets.

    Select cells J1, enter the following formula

    =SUMIF($D$1:$D$10,$I1,$G$1:$G$10)

    Drag (or paste) the formula in cells J1to J10. It will get pasted in cells J1 to J10, in all sheets.

    Click the tab of any sheet. Now your selection of all sheets is deactivated.

    In cell K1 of sheet1, enter the below  formula

    =SUM(Sheet1:Sheet5!J1)

    This formula is a 3D formula and will add cells J1 in all sheets from Sheet1 to Sheet5.

    Do let me know if this is what you wanted to do, or if you need any clarification.

    Regards,

    Vijaykumar Shetye,

    Spreadsheet Excellence,

    Panaji, Goa, India

    Monday, May 29, 2017 4:52 AM