Hi I'm doing an expense spreadsheet in google spreadsheet, and I am trying to do a if statement over a range of cells, from another sheet in the project.

I have one tab which is called Expenses 2015 and the other tab called Entries.  But when I try to do an if statement I get a formula parse error.

=IF(AND(Entries!A3:Entries!A1000 >= DATE(2015;1;1); Entries!A3 < DATE(2015;2;1)); if(Entries!C3 = "NET"; if(Entries!D3 > 0; Entries!D3; Entries!E3); 0); 0)

I get Formula Parse Error.
###### Who is Participating?

Commented:
The formula explicit only use either column D or column E, so if Bruno has one or more pays for Gas, then only column D is used in the sum.

Remove the if and criteria for column D, and make the formula with a sumproduct formula for column D + a sumproduct formula for column E.

Or change the setup on Entries, so that column D is the person who pays, and all pays are in column E.
Then make the sumproduct for column E.
0

Commented:
=IF(AND(Entries!A3:Entries!A1000 >= DATE(2015,1,1), Entries!A3 < DATE(2015,2,1)), if(Entries!C3 = "NET", if(Entries!D3 > 0, Entries!D3, Entries!E3), 0), 0)

try now
0

Commented:
if you attach the file, then it would be easy to spot the culprit comma or parenthesis
0

Author Commented:
Here is the file, I saved it into an Excel Spreadsheet.  I tried your problem but no luck.
Expenses.xlsx
0

Commented:
The range sizes in the formula has to be identical.
Guess you want the sum for each Tipo each month, so try this formula in B2.
``````=SUM(IF(Entries!\$A\$3:Entries!\$A\$1000 >= DATE(2015,COLUMN()-1,1),1,0)*IF(Entries!\$A\$3:Entries!\$A\$1000  < DATE(2015,COLUMN(),1),1,0)*IF(Entries!\$C\$3:Entries!\$C\$1000 = \$A2,1,0)*IF(Entries!\$D\$3:Entries!\$D\$1000 > 0, Entries!\$D\$3:Entries!\$D\$1000, Entries!\$E\$3:Entries!\$E\$1000))
``````
The formula has to be array entered Ctrl+Shift+Enter, then Excel adds {} brackets to show that it is an array formula.

The formula can be copied down and across, because it uses the Column() function to identify the month.
See attached.
Expenses.xlsx
0

Author Commented:
It works with excel, but when I insert into google spreadsheet, I still get formula parse error.
0

Commented:
Array formulas are somewhat different in google sheet.

Or use this formula instead, it is not an array formula
``````=IF(SUMPRODUCT((Entries!\$A\$3:Entries!\$A\$1000 >= DATE(2015,COLUMN()-1,1))*(Entries!\$A\$3:Entries!\$A\$1000  < DATE(2015,COLUMN(),1))*(Entries!\$C\$3:Entries!\$C\$1000 = \$A2)*Entries!\$D\$3:Entries!\$D\$1000)>0,SUMPRODUCT((Entries!\$A\$3:Entries!\$A\$1000 >= DATE(2015,COLUMN()-1,1))*(Entries!\$A\$3:Entries!\$A\$1000  < DATE(2015,COLUMN(),1))*(Entries!\$C\$3:Entries!\$C\$1000 = \$A2)*Entries!\$D\$3:Entries!\$D\$1000),SUMPRODUCT((Entries!\$A\$3:Entries!\$A\$1000 >= DATE(2015,COLUMN()-1,1))*(Entries!\$A\$3:Entries!\$A\$1000  < DATE(2015,COLUMN(),1))*(Entries!\$C\$3:Entries!\$C\$1000 = \$A2)*Entries!\$E\$3:Entries!\$E\$1000))
``````

See sheet
Expenses-sumproduct.xlsx
0

Author Commented:
I convert's but it get's still formula parse error.
0

Author Commented:
This is the formula it convert's to, but I still get a parse error.

``````=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(IF(Entries!\$A\$3:'Entries'!\$A\$1000 >= DATE(2015,COLUMN()-1,1),1,0)*IF(Entries!\$A\$3:'Entries'!\$A\$1000  < DATE(2015,COLUMN(),1),1,0)*IF(Entries!\$C\$3:'Entries'!\$C\$1000 = \$A2,1,0)*IF(Entries!\$D\$3:'Entries'!\$D\$1000 > 0, Entries!\$D\$3:'Entries'!\$D\$1000, Entries!\$E\$3:'Entries'!\$E\$1000))), 1, 1)
``````
0

Commented:
I am not that familiar with google sheets, but the formula looks ok.
Don't know why it does not work.
Try the non array formula with sumproduct.

``````=IF(SUMPRODUCT((Entries!\$A\$3:Entries!\$A\$1000 >= DATE(2015,COLUMN()-1,1))*(Entries!\$A\$3:Entries!\$A\$1000  < DATE(2015,COLUMN(),1))*(Entries!\$C\$3:Entries!\$C\$1000 = \$A2)*Entries!\$D\$3:Entries!\$D\$1000)>0,SUMPRODUCT((Entries!\$A\$3:Entries!\$A\$1000 >= DATE(2015,COLUMN()-1,1))*(Entries!\$A\$3:Entries!\$A\$1000  < DATE(2015,COLUMN(),1))*(Entries!\$C\$3:Entries!\$C\$1000 = \$A2)*Entries!\$D\$3:Entries!\$D\$1000),SUMPRODUCT((Entries!\$A\$3:Entries!\$A\$1000 >= DATE(2015,COLUMN()-1,1))*(Entries!\$A\$3:Entries!\$A\$1000  < DATE(2015,COLUMN(),1))*(Entries!\$C\$3:Entries!\$C\$1000 = \$A2)*Entries!\$E\$3:Entries!\$E\$1000))
``````

Another method for sumproduct with all entries as separate arrays.
The syntax --(...) converts the result True/False in the arrays to 1/0.
The multiplication method above does the same, but could be it does not work in google sheets.

``````=IF(SUMPRODUCT(--(Entries!\$A\$3:Entries!\$A\$1000 >= DATE(2015,COLUMN()-1,1)),--(Entries!\$A\$3:Entries!\$A\$1000  < DATE(2015,COLUMN(),1)),--(Entries!\$C\$3:Entries!\$C\$1000 = \$A2),Entries!\$D\$3:Entries!\$D\$1000)>0,SUMPRODUCT(--(Entries!\$A\$3:Entries!\$A\$1000 >= DATE(2015,COLUMN()-1,1)),--(Entries!\$A\$3:Entries!\$A\$1000  < DATE(2015,COLUMN(),1)),--(Entries!\$C\$3:Entries!\$C\$1000 = \$A2),Entries!\$D\$3:Entries!\$D\$1000),SUMPRODUCT(--(Entries!\$A\$3:Entries!\$A\$1000 >= DATE(2015,COLUMN()-1,1)),--(Entries!\$A\$3:Entries!\$A\$1000  < DATE(2015,COLUMN(),1)),--(Entries!\$C\$3:Entries!\$C\$1000 = \$A2),Entries!\$E\$3:Entries!\$E\$1000))
``````
0

Author Commented:
This is something I managed to get the google spreadsheet to work, I had to use ';' instead of ',' because of the locale settings, but, the third part when I remove I get passed, but when the third part is inserted I get failed, despite the cell A2 is equal to the cell Entries!A3:A1000, which should be a true not false.

``````=IF(AND(SUMPRODUCT(Entries!A3:A1000 >= DATE(2015;COLUMN()-1;1); Entries!A3:A1000 < DATE(2015;COLUMN();1); Entries!C3:C1000 = A3));"passed";"failed")
``````
0

Commented:
The formula is not correct.
Each part of the sumproduct formula returns TRUE/FALSE, and that is not possible to use in a multiplication, it has to be numbers, so sumproduct always returns 0, and the result will always be "failed"
TRUE/FALSE is converted to numbers by the syntax --(...)
AND is not needed, since there is only one element.

Using ; as delimiter the formula in B2 refering to A2="NET"
=IF(SUMPRODUCT(--(Entries!\$A\$3:\$A\$1000 >= DATE(2015;COLUMN()-1;1)); --(Entries!\$A\$3:\$A\$1000 < DATE(2015;COLUMN();1)); --(Entries!\$C\$3:\$C\$1000 = \$A2));"passed";"failed")
0

Author Commented:
I'm a bit baffled about how the sumproducts work, I usually develop in JavaScript, but I haven't done much in excel spreadsheeet or google spreadsheet.
0

Author Commented:
I tried to insert the formula but it still comes as failed.  I tried another formula, but I'm still baffled how this formula works.

``````=IF(SUMPRODUCT((Entries!\$A\$3:\$A\$1000 >= DATE(2015;COLUMN()-1;1)) * (Entries!\$A\$3:\$A\$1000  < DATE(2015;COLUMN();1)) * (Entries!\$C\$3:\$C\$1000 = \$A3) * (Entries!\$D\$3:\$D\$1000 > 0));SUMPRODUCT((Entries!\$A\$3:\$A\$1000 >= DATE(2015;COLUMN()-1;1)) * (Entries!\$A\$3:\$A\$1000  < DATE(2015;COLUMN();1))*(Entries!\$C\$3:\$C\$1000 = \$A3) * (Entries!\$D\$3:\$D\$1000 > 0));SUMPRODUCT((Entries!\$A\$3:\$A\$1000 >= DATE(2015;COLUMN()-1;1)) * (Entries!\$A\$3:\$A\$1000  < DATE(2015;COLUMN();1)) *(Entries!\$C\$3:\$C\$1000 = \$A3) * (Entries!\$D\$3:\$D\$1000 > 0)))
``````
0

Commented:
The translator fails, and the formulas needs a few modifications.
This formula with sumproduct works
``````=IF(SUMPRODUCT(--(Entries!\$A\$3:\$A\$1000 >= DATE(2015,COLUMN()-1,1)), --(Entries!\$A\$3:\$A\$1000 < DATE(2015,COLUMN(),1)), --(Entries!\$C\$3:\$C\$1000 = \$A2),Entries!\$D\$3:\$D\$1000),SUMPRODUCT(--(Entries!\$A\$3:\$A\$1000 >= DATE(2015,COLUMN()-1,1)), --(Entries!\$A\$3:\$A\$1000 < DATE(2015,COLUMN(),1)), --(Entries!\$C\$3:\$C\$1000 = \$A2),Entries!\$D\$3:\$D\$1000),SUMPRODUCT(--(Entries!\$A\$3:\$A\$1000 >= DATE(2015,COLUMN()-1,1)), --(Entries!\$A\$3:\$A\$1000 < DATE(2015,COLUMN(),1)), --(Entries!\$C\$3:\$C\$1000 = \$A2),Entries!\$E\$3:\$E\$1000))
``````

And this array formula also works
``````=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(IF(Entries!\$A\$3:\$A\$1000 >= DATE(2015,COLUMN()-1,1),1,0)*IF(Entries!\$A\$3:\$A\$1000 < DATE(2015,COLUMN(),1),1,0)*IF(Entries!\$C\$3:\$C\$1000 = \$A2,1,0)*IF(Entries!\$D\$3:\$D\$1000 > 0, Entries!\$D\$3:\$D\$1000, Entries!\$E\$3:\$E\$1000))), 1, 1)
``````
0

Author Commented:
I just the formula to work, but now I have been battling to try to add two cells and rows together depending on the criteria.  Let's say if Bruno Pay's \$20 for Gas in the month of January, and Vanessa pay's a total of \$20 in January all in row D3 & D7, but only add for the condition of GAS, the other's is not to count in the addition, I don't know if there is another special formula for it.  Here is the formula I got to work.

``````=IF(
SUMPRODUCT((Entries!\$A\$3:\$A\$1000 >= DATE(2015;COLUMN()-1;1)) * (Entries!\$A\$3:\$A\$1000 < DATE(2015;COLUMN();1))) * (Entries!\$C\$3:\$C\$1000 = A3) * Entries!\$D\$3:\$D\$1000;
SUMPRODUCT((Entries!\$A\$3:\$A\$1000 >= DATE(2015;COLUMN()-1;1)) * (Entries!\$A\$3:\$A\$1000 < DATE(2015;COLUMN();1)) * (Entries!\$C\$3:\$C\$1000 = A3) * Entries!\$D\$3:\$D\$1000);
SUMPRODUCT((Entries!\$A\$3:\$A\$1000 >= DATE(2015;COLUMN()-1;1)) * (Entries!\$A\$3:\$A\$1000 < DATE(2015;COLUMN();1)) * (Entries!\$C\$3:\$C\$1000 = A3) * Entries!\$E\$3:\$E\$1000))
``````
0

Author Commented:
Finally I got it, I basically created a third column, I added both the sum of Column D & E to Column F and I inserterted this formula.

``````=SUMPRODUCT(Entries!\$F\$3:\$F\$1000 * (Entries!\$A\$3:\$A\$1000 >= DATE(2015;COLUMN()-1;1)) * (Entries!\$A\$3:\$A\$1000 < DATE(2015;COLUMN();1)) * (Entries!\$C\$3:\$C\$1000 = A3))
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.