SumIF from another sheet

05/10/21
Answered - Pending Review

Hi,

I have a formula that sums up the expenses for a particular billing code, like this:

=SUMIF([Account Code]:[Account Code], "539023", [Expenses Submitted]:[Expenses Submitted])

This works well, however, I want to use this code on a different sheet, without having to link to a bunch of cells on the first sheet. I can't seem to get the sheet references to work (I don't want to put in a range, I want it to sum everything with that account code, as lines are added to the sheet from a form).

Thanks.

Popular Tags:

Answers

  • Bassam KhalilBassam Khalil ✭✭✭✭✭

    Hi @Barent Wagar 

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful". 

  • Thanks, I hid a bunch of columns, basically I want to replicate what I did on the second sheet but with a reference, not a link to a cell.



    Thanks.

  • Hi @Barent Wagar

    We can replicate your exact same formula but change it to be a cross-sheet formula instead of an in-sheet one. The way to do this is to simply swap out your current column references that look like this:

    [Account Code]:[Account Code]

    to be a cross-sheet range that looks down that one specific column:

    {Account Code in Source Sheet}


    Since your current sheet has a column for the Account Code, you can replace the number "in these" with a cell reference to your current sheet's column, like so:

    "539023" becomes [Account Code]@row


    The final formula should look something like this:

    =SUMIF({Account Code in Source Sheet}, [Account Code]@row, {Expenses Submitted in Source Sheet})


    This will only SUM together data in the Expenses Submitted Column for the rows that match the Account Code you place in the cell in that row.

    Here are some Help Center articles that may help as you build this out:


    Let me know if this works for you or if I can clarify anything further!

    Cheers,

    Genevieve

  • Awesome, thanks!

Sign In or Register to comment.