# Help with IF Statement with multiple SUMIFS

Hello,

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))

Tags:

• ✭✭✭✭✭✭

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.

Thanks

Paul

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!