Solved

google spreadsheet multiple range of cells in if statement

Posted on 2015-02-05
17
224 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
The viewer will learn how to edit text. This includes Font, Spacing, Resizing, Color, and other special text options.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

758 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

18 Experts available now in Live!

Get 1:1 Help Now