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!

Find more posts tagged with

Finance

Formulas

Accepted answers

All comments

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!

Jonathan Rinkerman

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.

Screen Shot 2021-07-30 at 4.56.47 PM.png

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.

Screen Shot 2021-07-30 at 4.58.59 PM.png

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

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

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å

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.