Solved

Posted on 2015-02-05
302 Views
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.
0
Question by:cinco-pata5
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 9
• 6
• 2

LVL 26

Expert Comment

ID: 40592059
=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

LVL 26

Expert Comment

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

Author Comment

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

LVL 22

Expert Comment

ID: 40595579
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 Comment

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

LVL 22

Expert Comment

ID: 40596217
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 Comment

ID: 40596965
I convert's but it get's still formula parse error.
0

Author Comment

ID: 40596985
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

LVL 22

Expert Comment

ID: 40597111
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 Comment

ID: 40597380
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

LVL 22

Expert Comment

ID: 40598903
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 Comment

ID: 40606035
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 Comment

ID: 40606046
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

LVL 22

Expert Comment

ID: 40606291
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 Comment

ID: 40607087
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

LVL 22

Accepted Solution

Ejgil Hedegaard earned 500 total points
ID: 40608168
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

Author Comment

ID: 40610143
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer aâ€¦
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo â€¦
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦
###### Suggested Courses
Course of the Month5 days, 9 hours left to enroll