Summing rows if columns are checked using cross-sheet reference

Hello,

This is the formula I've tried but it is giving me an "unparseable" error.

=SUMIF({Tracer Site List -Revenue/Project Complete},[Project Complete]:[Project Complete], 1, [Licensing Fee]:[Licensing Fee])


Thank you for your assistance!

Tags:

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 05/04/22 Answer ✓

    @deb_63_hydracor

    Ok, here's what I think you did here. You created the reference to the other sheet and selected both the Project Complete column and the Licensing Fee column together as your range {Tracer Site List -Revenue/Project Complete}, and then attempted to call out each column inside the range.

    What you will want to do to make this work is to create a separate range for each column as your write your formula:

    =SUMIF({Tracer Site List Project Complete range}, 1, {Tracer Site List Licensing Fee range})

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @deb_63_hydracor

    Right off the bat I can tell you that you have too many elements for SUMIF. For the function, the syntax is SUMIF( range , criterion, [sum_range]), where you have SUMIF( range, range, criterion, sum range).

    The next thing is that you have a range from another sheet, then two ranges from the sheet you're on. This won't work, even if you had the syntax right.

    I am assuming you want to add up the values in the Licensing Fee column for rows that have checked box in the Project Complete column, so how does this range {Tracer Site List -Revenue/Project Complete} fit in to the criteria?

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 05/04/22 Answer ✓

    @deb_63_hydracor

    Ok, here's what I think you did here. You created the reference to the other sheet and selected both the Project Complete column and the Licensing Fee column together as your range {Tracer Site List -Revenue/Project Complete}, and then attempted to call out each column inside the range.

    What you will want to do to make this work is to create a separate range for each column as your write your formula:

    =SUMIF({Tracer Site List Project Complete range}, 1, {Tracer Site List Licensing Fee range})

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!