Solved

google spreadsheet multiple range of cells in if statement

Posted on 2015-02-05
17
242 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 25

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 25

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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office 2010 is Microsoft's latest edition to their office productivity collection. It "promises to include rich and powerful new ways to deliver your best work". So to start off, I have to say that I'm slightly disappointed... That bei…
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…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

912 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now