Help with IF Statement with multiple SUMIFS

Hardi Australia IT

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

Please let me know if any more information is needed.

Thank you in advance :)

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.

Thanks

Paul