Help with IF Statement with multiple SUMIFS


I am trying to sum up a range of data from two spreadsheets based on the same criteria.

Here is my formula which is throwing an #UNPARSABLE error and I cannot understand why

=IF(SUMIFS({Advertising Total Cost}, {Hardi}, =$Region$1, {Year}, =2021, {Month}, =[OCT-21]$1, {contract}, =CATEGORY@row) AND(SUMIFS({misc spend}, {misc hardi}=$Region$1, {misc year}, =2021, {misc month}=[OCT-21]$1, {misc contract}=CATEGORY@row)) =0,"",SUMIFS({Advertising Total Cost}, {Hardi}, ="HARDI", {Year}, =2021, {Month}, =[OCT-21]$1, {contract}, =CATEGORY@row) + SUMIFS({misc spend}, {misc hardi}="HARDI", {misc year}, =2021, {misc month}=[OCT-21]$1,{misc contract}=CATEGORY@row))

Please let me know if any more information is needed.

Thank you in advance :)


  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭

    Hi @Hardi Australia IT

    Looks like you have a few things in the formula there.

    The SUMIFS criteria references don't require '=' as the cell they are referencing dictates what criteria they should be.

    Some of the SUMIFS formulas are missing the commas / using '=' instead - SUMIFS({misc spend}, {misc hardi}="HARDI" instead of SUMIFS({misc spend}, {misc hardi},"HARDI".

    Also the AND statement in the middle isn't correctly placed or formulated

    Looking at the formula i think the below is what you were looking to achieve, i substituted the AND formula for a SUM just to pull the totals together.

    =IF(SUM(SUMIFS({Advertising Total Cost}, {Hardi}, $Region$1, {Year}, 2021, {Month}, [OCT-21]$1, {contract}, CATEGORY@row),SUMIFS({misc spend}, {misc hardi},$Region$1, {misc year}, 2021, {misc month},[OCT-21]$1, {misc contract},CATEGORY@row))=0,"",SUM(SUMIFS({Advertising Total Cost}, {Hardi},"HARDI", {Year}, 2021, {Month}, [OCT-21]$1, {contract}, CATEGORY@row),SUMIFS({misc spend}, {misc hardi},"HARDI", {misc year}, 2021, {misc month},[OCT-21]$1,{misc contract},CATEGORY@row)))

    Freehanded with no testing so hoping it works or gets you closer to your solution.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!