Why am I getting Invalid Operations or Inparseable with Sumifs

Phipps Lawrence

Hi Everyone,

So update I was able to figure out the equation however it will not work and says Unparseable or Invalid Operation Error when I try it in two different formats. I basically need to sum up all the children and only show an anticipated spend over the next 90 days. The equation I am using that gets inparseable is =SUMIFS({Project Plan Range 3}{Project Plan Range 6}{Project Plan Range 7}{Project Plan Range 9}{Project Plan Range 8}{Project Plan Range 14}{Project Plan Range 15}{Project Plan Range 16}{Project Plan Range 17}{Project Plan Range 18}{Project Plan Range 19}{Project Plan Range 20}{Project Plan Range 21}{Project Plan Range 22}{Project Plan Range 23}{Project Plan Range 24},{Project Plan Range 25}{Project Plan Range 26}{Project Plan Range 27}{Project Plan Range 28}{Project Plan Range 29}{Project Plan Range 10}{Project Plan Range 30}{Project Plan Range 31}{Project Plan Range 32}{Project Plan Range 33}{Project Plan Range 34}{Project Plan Range 35}{Project Plan Range 36}{Project Plan Range 37}{Project Plan Range 38}{Project Plan Range 39}{Project Plan Range 40},>=TODAY(),Project Plan Range 25}{Project Plan Range 26}{Project Plan Range 27}{Project Plan Range 28}{Project Plan Range 29}{Project Plan Range 10}{Project Plan Range 30}{Project Plan Range 31}{Project Plan Range 32}{Project Plan Range 33}{Project Plan Range 34}{Project Plan Range 35}{Project Plan Range 36}{Project Plan Range 37}{Project Plan Range 38}{Project Plan Range 39}{Project Plan Range 40},<=TODAY()+90)). 

Reason it is so long is because I do not want to sum up the parent cells so I have to only do the children. When I only sum the children it says Unparseable but when I sum up the entire columns it shows Invalid Operations Error. The equation I use that reads Invalid Operations is: =SUMIFS({Project Plan Range 1}, {Project Plan Range 2}, >=TODAY(), {Project Plan Range 2}, <=TODAY() + 90). I have attached a screenshot of a sample amount of cells I am looking to add in. Any help would be greatly appreciated because I do not know why it is not working. 

Paul Newcome

Insert a helper column on the source sheet and use a formula to automatically check the box on child rows. Then your SUMIFS becomes MUCH more simple.

=SUMIFS({Entire Column To Sum}, {Entire Helper Column}, @cell = 1, {Entire Date Column}, @cell <= TODAY(90))

Paul Newcome

Insert a helper column on the source sheet and use a formula to automatically check the box on child rows. Then your SUMIFS becomes MUCH more simple.

=SUMIFS({Entire Column To Sum}, {Entire Helper Column}, @cell = 1, {Entire Date Column}, @cell <= TODAY(90))

Phipps Lawrence

@Paul Newcome thank you so much for your help and quick reply! This resolved all the issues I had and made the equation significantly shorter

Paul Newcome

Happy to help. 👍️