Solved

google spreadsheet multiple range of cells in if statement

Posted on 2015-02-05
17
267 Views
Last Modified: 2015-02-14
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
Comment
Question by:cinco-pata5
  • 9
  • 6
  • 2
17 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
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

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

Author Comment

by:cinco-pata5
ID: 40592673
Here is the file, I saved it into an Excel Spreadsheet.  I tried your problem but no luck.
Expenses.xlsx
0
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 
LVL 21

Expert Comment

by:Ejgil Hedegaard
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))

Open in new window

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

by:cinco-pata5
ID: 40595861
It works with excel, but when I insert into google spreadsheet, I still get formula parse error.
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40596217
Array formulas are somewhat different in google sheet.
Try uploading the Excel sheet and see if it can convert.

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

Open in new window


See sheet
Expenses-sumproduct.xlsx
0
 

Author Comment

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

Author Comment

by:cinco-pata5
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)

Open in new window

0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
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))

Open in new window

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

Open in new window

0
 

Author Comment

by:cinco-pata5
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")

Open in new window

0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
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

by:cinco-pata5
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

by:cinco-pata5
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)))

Open in new window

0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40606291
I uploaded the sheet to google sheets to test.
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))

Open in new window


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)

Open in new window

0
 

Author Comment

by:cinco-pata5
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))

Open in new window

0
 
LVL 21

Accepted Solution

by:
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

by:cinco-pata5
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))

Open in new window

0

Featured Post

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Automation of Autosum 23 120
Excel Array formula Help 11 62
Open Office very very slow 3 156
OneDrive for Business:  Redirected Folder or Network Drive natively? 3 107
Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
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 …

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question