New Bug Encountered: cross-sheet sumifs formula shifts the result before doing a sum

[Akram]

I have 2 sheets in a workbook on google drive
Sheet 1, Sheet 2

In Sheet 1: there is a sumifs formula that adds value from Sheet2 based on a simple condition (example-
In Sheet1 : Cell A10 has “Bob”

Cell B10 has formula to sum all cells corresponding to Bob in Sheet2).
=sumifs(Sheet2!B:B,Sheet2!A:A,Sheet1!A10)

Sheet2:
Row Number 1 Name Expense
Row Number 2 Bob 1000
Row Number 3 Kirstin 20100
Row Number 4 Bob 2000
Row Number 5 Asuncion 15000
Row Number 6 Paulina 3000
Row Number 7 Paulina 3000
Row Number 8 Many 1000
Row Number 9 Otilia 1000
Row Number 10 Paulina 2000
Row Number 11 Delta 1000

Google sheet is correctly adding row number 2 & 4 (1000+2000 = 3000)
But, Appsheet is showing addition of row number 3 & 5 (20100+15000 = 35100)

Also, If I just clear the cell B3 in Sheet2, Appsheet is showing addition of row number 4 & 6 (2000+3000 = 5000)

[Aleksi]

Have you thought to use an app formula with a virtual column?

[Akram]

Virtual column would mean I have to rewrite the entire excel formulas in app formula format, and build an app from scratch. This defeats the purpose of no-code platform like appsheet which is supposed to convert excel file into app in few steps.

If I had to spend 2 days to debug & identify this bug. Basically, results in my sheet are different than results shown in app.

BTW-
I also tried using query formula in google sheet today. Google sheet provides correct output in both cases - (SUMIFS as well as QUERY)

Using Query formula in google spreadsheet gives below error.

Unable to fetch app definition. Error: The spreadsheet for ‘NGsystemV5-1120298:1.000029:Actual Oct2019’ uses an unsupported formula : Excel function ‘__xludf.DUMMYFUNCTION’ is not supported in formulas.

[WillowMobileSys]

@Akram

I have built several apps that relied very heavily on Queries and formulas within the Google sheets, combining several sheets of data together for the results to produce many views within an AppSheet app. I have never experienced the issues you are describing.

At its core, AppSheet simply displays the resulting values in the Google sheet. However, keep in mind that changes made directly to the Google sheet are NOT AUTOMATICALLY updated within the app.

If the AppSheet app itself posts a change, it will wait for sheet formulas to complete and then retrieve results. BUT if the change is made in the sheet itself or some external source changes the sheet, the app does not know of such changes. In this case, a manual Sync request is required to refresh the data from the sheet within the app.

---

If a manual Sync, isn’t the answer to your issue, then in order for us to help, we will need a few more details about the app.

1. Can you provide a snapshot of the sheet and of the AppSheet table definition that displays that sheet data?

2. Snapshots examples of the issue you are seeing would be the most helpful as well.

[Akram]

Here’s my file

drive.google.com

nextg template (1).xlsx

Google Drive file.

Sheet used in Table : Oct 2019
Column E to Column K are inputs
Remaining columns are read-only

Try creating an app with this file

[Akram]

Column A is Employee ID

After you create the file,
Compare values displayed in Column S - Values are different in google spreadsheet vs appsheet app

Look at these Employee IDs:
7 Rehman
8 Habib
10 Siraj
11 OP Yadav
14 Mehmood
17 Govind
18 Shesram
19 Rohit yadav
21 Moinuddin
22 Bhola
25 Hanif
29 Kaushal
33 Arvind S
36 Javed
40 mahesh verma

[WillowMobileSys]

Yes, I see your issue. What is happening is AppSheet is loading the spreadsheet formulas. It then re-calculates the values with these formulas upon a Sync of the app. In my case the loaded formulas were incorrect, maybe because I hadn’t loaded the other sheets the formulas relied on? I’m not sure.

Having Formulas in the app makes Sync’s painfully slow. I don’t use sheets in this way. If I have formulas, I let formulas in the sheet dictate the values and then use the app to simply display and interact with those values. Most of my apps are built with NO formulas int eh sheets at all.

I did try removing the loaded formulas from the AppSheet columns but the values still did not update to reflect what was in the sheet. I am not sure why yet.

I’ll play with it a little more to try to understand what is going on.

[Akram]

Thanks for your efforts John.
Exactly what you said - Appsheet re-calculates the the formulas, and gives a different result

I have also loaded other sheets in my app, but the result was same.

Try this…
Change the value in Cell F4 of sheet ‘increase master’ (Employee ID : 11 OP Yadav)
And see the effect on…
Cell S12 in sheet ‘Oct2019’ (Employee ID : 11 OP Yadav)
Spreadsheet behaves correctly

Look at corresponding value in appsheet for Employee ID : 11 OP Yadav
There is no change.
But, Look at corresponding value in appsheet for Employee ID : 17 Govind

Appsheet calculates as if you updated Employee ID : 17 Govind ( which is just above Employee ID : 11 OP Yadav in sheet ‘Increase master’)

I am attaching the screenshot of appsheet & google spreadsheet .

[Akram]

This is how Appsheet recalculates…Refer image
Example… Look at Green Line in image
If you change cell F15 in sheet ‘increase master’ for Employee ID : 22 Bhola , spreadsheet updates correctly
But Appsheet calculates updates it for Employee ID : 40 mahesh verma

[WillowMobileSys]

I played a bit more. First I trimmed it down so I only had the Employee #, Employee Name and the “extra” columns. Still had the problem with AppSheet calcs.

I then re-created this trimmed down version in a Google sheet. The calculations reflected perfectly in the app. I compared the sheet formula loaded into the column and it was exactly the same between the two apps.

So, I now think there is some issue with the way these types of formulas from Excel are being utilized within AppSheet.

I would recommend you forward this issue to support@appsheet.com

[pravse]

The core problem is that Excel formulas are computed by our system whereas Google Sheets formulas are computed by Google.

Our formula engine is not as thorough or bug-free or comprehensive as what is in Excel or Google Sheets.

Is it an option at all for you to use Google Sheets instead of Excel?