Solved

google spreadsheet multiple range of cells in if statement

Posted on 2015-02-05
17
333 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
[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
  • Learn & ask questions
  • 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
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

 
LVL 23

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 23

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 23

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 23

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 23

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 23

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

Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

Question has a verified solution.

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

In this article I will provide some simple productivity hacks that will help you use Google to specifically show results from any web site (Experts-Exchange.com in my example), with minimal effort in Chrome and Firefox. I've seen a common theme a…
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…
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

632 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