Getting error in a countif formula

Hi all,

I am trying to expand on my regular countif formula but I am getting an error. The original formula:

=IF(Order@row = 2, SUMIFS({Costs}, {Date}, Date@row, {Location}, location@row), SUM(CHILDREN()))

works perfectly fine. However, when I add onto this formula, I get an error:

=IF(Order@row = 2, SUMIFS({Costs}, {Date}, Date@row, {Location}, location@row, {Approval}, "Submitted" ), SUM(CHILDREN()))

I know I've run into issues when it comes to including a date criteria in my formulas but I'm not sure if this is in the same vein as previous times. Anyone have any thoughts on how to get around the error?

Find more posts tagged with

Formulas

Accepted answers

All comments

Ray Lindstrom

@kioshi43,

You're missing criteria to make this "logical".

{Approval}, "Submitted"

When looking at the Reference named {Approval}, what needs to match (or equal) "Submitted"?

Maybe try:

{Approval}, @cell="Submitted"

Hope this helps! If not, please mark it as not being the answer, and your question will return to the unanswered queue for others to evaluate.

BRgds,
-Ray

kioshi43

Just tried the @cell="Submitted" but that still resulted in the same error. Appreciate you chiming in though!

kioshi43

@Ray Lindstrom I tried hitting no on the response but it doesn't seem to be doing anything haha

kioshi43

Actually.. disregard, I see it in the queue.

JuliannaK

You can try:

[Approval]:[Approval], @cell="Submitted"

kioshi43

The approval column is in another sheet and the @cell="Submitted" still results in the error unfortunately. Thanks for chiming in though!

Jovana.Perkovic

Hi @kioshi43 ,

The error you’re seeing usually happens because all ranges in a SUMIFS formula must be the same size and come from the same sheet. When you added {Approval}, Smartsheet likely found that this range didn’t align with {Costs}, {Date}, and {Location}.

✅ Steps to Troubleshoot

Check all ranges
- {Costs}, {Date}, {Location}, and {Approval} must:
  - Come from the same source sheet.
  - Have the same number of rows.
Verify references
- Go to Data → Manage References and confirm each reference points to the correct column.
Confirm row alignment
- No missing rows in one column compared to others.

Use quick checks, Counts should match :

=COUNT({Costs})

=COUNT({Approval})

4. Validate criteria
- Ensure "Submitted" matches exactly (case-sensitive, no extra spaces).

5. Rebuild references if needed
- If any range is off, delete and recreate it via Manage References

✅ Fixed Formula to Try

Once ranges are aligned, this should work:

=IF(Order@row  = 2, SUMIFS({Costs}, {Date}, Date@row , {Location}, location@row , {Approval}, "Submitted"), SUM(CHILDREN()))

Hope this helps! Let me know if it worked :)

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.