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}, =[OCT21]$1, {contract}, =CATEGORY@row) AND(SUMIFS({misc spend}, {misc hardi}=$Region$1, {misc year}, =2021, {misc month}=[OCT21]$1, {misc contract}=CATEGORY@row)) =0,"",SUMIFS({Advertising Total Cost}, {Hardi}, ="HARDI", {Year}, =2021, {Month}, =[OCT21]$1, {contract}, =CATEGORY@row) + SUMIFS({misc spend}, {misc hardi}="HARDI", {misc year}, =2021, {misc month}=[OCT21]$1,{misc contract}=CATEGORY@row))
Please let me know if any more information is needed.
Thank you in advance :)
Answers

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}, [OCT21]$1, {contract}, CATEGORY@row),SUMIFS({misc spend}, {misc hardi},$Region$1, {misc year}, 2021, {misc month},[OCT21]$1, {misc contract},CATEGORY@row))=0,"",SUM(SUMIFS({Advertising Total Cost}, {Hardi},"HARDI", {Year}, 2021, {Month}, [OCT21]$1, {contract}, CATEGORY@row),SUMIFS({misc spend}, {misc hardi},"HARDI", {misc year}, 2021, {misc month},[OCT21]$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
Categories
Check out the Formula Handbook template!