Cross-Sheet SUMIFS is returning #UNPARSEABLE

Hello,

I'm having an issue with a cross sheet formula. I'm trying to build a budget tracking system in two parts. It involves a top sheet with a the sum of all expenses by category and an expense log that is a running list of expenses.

I tested the following SUMIFS in the expense log and it works correctly.Where it is returning the total amount for an editor that fits these criteria.

=SUMIFS(Amount:Amount, [Expense Category]:[Expense Category], "Personnel", [Hiring Status]:[Hiring Status], "Freelance", Department:Department, "Editorial/Production", Position:Position, "Editor", [Payment Status]:[Payment Status], "Actual")

However when I try to reference the expense log and use the same formula in the top sheet I get #UNPARSEABLE.

=SUMIFS({Test Expense Log Range 1}, (Amount:Amount, [Expense Category]:[Expense Category], "Personnel", [Hiring Status]:[Hiring Status], "Freelance", Department:Department, "Editorial/Production", Position:Position, "Editor", [Payment Status]:[Payment Status], "Actual"))

Is there a step I'm missing to get the formula to work?

Thank you!

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Rinks

    I hope you're well and safe!

    You can't mix cross-sheet and local ranges.

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Does that mean that COUNTIF or SUMIF would not work with cross-sheet?

    The workflow basically breaks down to:

    1: I have a form that intakes data based on our expenses and adds a row to the expense log with pertinent drop down information.

    2: Some of our expenses have duplicate titles based on department, category, hiring status, expense type etc

    3.Some of the expenses are anticipated and haven't been completed yet. Hence the Actual vs Commitment column.

    Example expenses in that sheet. As you can see the sheet summary using SUMIFS is able to parse the data correctly using variations of the following formula.

    =SUMIFS(Amount:Amount, [Expense Category]:[Expense Category], "Personnel", [Hiring Status]:[Hiring Status], "Freelance", Department:Department, "Editorial/Production", Position:Position, "DP", [Payment Status]:[Payment Status], "Actual")


    On the Top Sheet Sheet there is a row for Personnel->Freelance->Editorial/Production->DP. I would like to have the actual and commitments calculate on the top sheet like they did in the sheet summary for the expense log.


    Currently I was using the following formula but let me know if this is even possible.

    =SUMIFS({Test Expense Log Range 1}, (Amount:Amount, [Expense Category]:[Expense Category], "Personnel", [Hiring Status]:[Hiring Status], "Freelance", Department:Department, "Editorial/Production", Position:Position, "DP", [Payment Status]:[Payment Status], "Actual"))

    Would it make more sense to have a SUMIFS on the expense log and have that information get pulled to the top sheet?

  • Jonathan Rinkerman
    Jonathan Rinkerman ✭✭✭
    edited 08/02/21

    I think I was able to make it work! I didn't realize that you could reference each column individually using cross sheet. I used the following code and it worked! I renamed the references so that typing them out for all the other cells would get confusing / tedious.


    =SUMIFS({Amount}, {Expense Category}, "Personnel", {Hiring Status}, "Freelance", {Department}, "Editorial/Production", {Position}, "DP", {Payment Status}, "Actual")

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Jonathan Rinkerman

    Excellent!

    Happy that you figured it out!

    Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!