Best answer

Excel cell with IF statement returns zero to Zapier Updated Excel row function

  • 23 March 2021
  • 8 replies
  • 266 views

Hello,

I have an Excel spreadsheet in Onedrive. Every time a new row is added (values will then be filled in columns A-H), a formula in column I checks that the row is not empty. It then sums the values of column H for all rows that meets certain conditions (col F=false and col D<todays date). Everything works like a charm in Excel and looks like this:

 

When I try to fetch the rows with the Zapier Updated Excel row function, all values works fine except for the values in column I and J that is created with IF-statements like the one described above for col I. They returns 0 in Zapier, although visible existing in Excel. Please notice that the values from columns E, G and H is fetched correctly to Zapier:

I have also tried shifting between different rows in the test trigger, 0 in all col I and J

 

Am I doing something wrong in Zapier, or does Zapier not recognize values from cells that is filled with a combination of IF and SUMIFS statements? I have tried creating another column in Excel that fetches the data from col I with the =TEXT function, but that column also returned 0 to Zapier.

I may mention that the values in col A-H are also fetched from another sheet with formulas, and they seem to work fine. The overall goal is to send an e-mail including the values from col A, I and K when the row is updated. Everything with the e-mail works, except that the I value is a 0.

Best regards,
Martin

icon

Best answer by arleskr 29 April 2021, 14:10

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

8 replies

Userlevel 5
Badge +6

@arleskr,

What happens when you add an additional find a row step. Do the values show up in Zapier then? 

@arleskr,

What happens when you add an additional find a row step. Do the values show up in Zapier then? 


Unfortunately I get exactly the same results when I add a find row step: Zero values in col I and J
 

 

Userlevel 5
Badge +6

@arleskr,

Thank you for the additional screenshot. Would you be able to include column A as the Lookup Value and then do a test to see if that brings you the latest details of the row? 

@arleskr,

Thank you for the additional screenshot. Would you be able to include column A as the Lookup Value and then do a test to see if that brings you the latest details of the row? 


Thanks for the suggestion. I tried with col A as the lookup column/value, but the results in col I and J are still zero.

Lookup column and Lookup value is col A

 

Row is found, but with zero in Col I and J

 

Userlevel 5
Badge +6

And to confirm, you do have the correct value in the actual file? That’s strange. 

And to confirm, you do have the correct value in the actual file? That’s strange. 


Absolutely, you can see the Excel file here: https://1drv.ms/x/s!Avq-EU6aQ5oteynIFMBBUKzv-js?e=yUaX1Y

The Values I try to fetch is in column “I” (where every row has value 30) on the Statistik worksheet

I finally found the answer to this problem!

When I log on to Onedrive, it shows me Excel Online in my native language (Swedish). That means that the formulas are written in Swedish.

When Zapier looks at the excel document, it gets it as a Guest, meaning that the original English language setting is used. In that case all formulas is automatically translated into their English equivalent.

In one of my formulas that created the value that Zapier got as “0”, I had a Swedish word in the formula:

col F=”FALSKT” (meaning FALSE in Swedish) and col D<todays date

As the “FALSKT” part was in quote marks, that part of the formula didn’t automatically translate to English in the english view only version of the document where Zapier gets the information.

When I changed false/true for numbers (0/1) instead, everything started working as a charm :)

Userlevel 7
Badge +11

Hey @arleskr!

So glad you found a solution to that :) Thanks for sharing it with the Community!