Monthly Analysis stopped working

Google Sheets
1

Error SWITCH has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 171, column count: 1.

My Monthly Analysis sheet start throwing this error on all calculated values.

=ARRAYFORMULA(IF(ISTEXT($A30:$A),SWITCH($O30:$O,“Type”,IF($A30:$A=“Income”,(AN3*$P$20)+(BR3*$P$21),(AN4*$P$20)+(BR4*$P$21)),“Income”,(VLOOKUP($A30:$A&“Income”,{$BA2:$BA&$AZ2:$AZ,BC2:BC},2,FALSE)$P$20)+(VLOOKUP($A30:$A&“Income”,{$BO$2:$BO&$BP$2:$BP,BR$2:BR},2,FALSE)$P$21),“Expense”,(VLOOKUP($A30:$A&“Expense”,{$BA2:$BA&$AZ2:$AZ,BC2:BC},2,FALSE)$P$20)+(VLOOKUP($A30:$A&“Expense”,{$BO$2:$BO&$BP$2:$BP,BR$2:BR},2,FALSE)$P$21),“Category”,(VLOOKUP($A30:$A,{$AK7:$AK,AN7:AN},2,FALSE)$P$20)+(IFERROR(VLOOKUP($A30:$A,{INDIRECT($U$3),INDIRECT(VLOOKUP(C26,{$P$2:$P$13,$R$2:$R$13},2,FALSE))},2,FALSE),0))$P$21),IFERROR(1/0)))

Any idea what might have caused this? And how to fix it? I don’t recall making any specific change to trigger this.

8 Likes
2

As a reference, I added Monthly Analysis to a clean Tiller Foundation sheet and it exhibits the same error.

3

I can echo this as well, just not for Monthly Analysis. Seeing the same errors for Bill Payment Tracker as of this morning. Yesterday was fine.

4

Same issue with Monthly Analysis which is one of my favorite sheets. It was fine yesterday

5

Must be a change within Google on how the SWITCH function works. Anyone at Tiller able to fasttrack a support request with the google sheets team?

6

Hi @ken.barker and @wjthomas42 for flagging this.

It does look like something is not right. That’s an interesting suggestion that it might be the SWITCH function.

I don’t have a contact at Google Sheets, but I’m flagging it internally and some others have those contacts I think.

Jon

7

Having the same issue. I noticed that it only is a problem with the Income type, not with Expenses.

I checked both sheets I have linked to Tiller and an archived sheet I haven’t touched for months. Same issue in all.

8

Quick update… @jono and I chatted about this earlier today and he is going to have a look at it later in the week. Thanks for flagging.

9

I got the same error.

10

Just throwing this out there for the commenters here.
Is it true that either the Monthly Analysis or the Bill Payment Tracker sheet WORKED fine for you in the past. And you didn’t make any changes. And now, you are getting these errors in the past few days?

And if and when you try to update the sheet or get a new sheet from Tiller Labs add-on, the new sheet has the same problem?

1 Like
11

Yes @jono , that is correct.

I deleted the monthly analysis sheet and re-added and the error persisted.

12

I just looked and my MA tab is messed up too, same as what the others are saying.

13

Yes. @jono Tried it on a new sheet with the same issue.

14

Yes and yes. The problem is in multiple files, including archived files from last year. Also tried repairing the sheet and the problem persisted. I mentioned this upthread - noticed that this only seems to happen when I select Income as the type. Expenses display as expected even though the code in the cell is the same (i.e. works with the SWITCH function).

Also - I first thought that it was something I’d done (before looking at the archived sheets) so I tried changing every instance of $A30:$A to just $A30 and it showed the totals on that line (but not subsequent rows obviously.)

15

My tab appears to be working now.

16

Both my “production” and my test tabs are still exhibiting the issue.

1 Like
17

This is correct. I appreciate you looking into it!

18

I’m also getting the exact same SWITCH error.

19

A quick update. It looks like with a SWITCH() inside an ARRAYFORMULA(), if there is a range in an IF statement, the formula no longer works. I’m pretty sure this would work in the past.

This type of formula is part of the Monthly Analysis sheet.

switch test@2x
switch test@2x3360×500 104 KB

I’m going to do a little more research before trying change the formulas in the sheet as it seems there has been a change on Google’s end, whether intended or not.

Jon

2 Likes
20

@jono Thank you for looking. +1 for issues with the MA sheet. I can confirm, without a doubt, I have not made any changes other than adding new transactions in the last few days/weeks. Also, for me, it looks like Jan & Feb are okay (at least no errors) while March is messed up.

Thanks again