In Google Sheets, I'm using IMPORTRANGE to pull in data from to a large sheet.

One field is a dollar amount. I would like to import the dollar amount and replace any zero amount with a specific dollar amount. For example, if the number is $100, I want to import $100. But if the number is $0, I want to import it but replace the $0 with $50.

  • you can use simple IF formula for that, like:

    =IF(IMPORTRANGE("ID", "Sheet1!A1")="$0", "$50", IMPORTRANGE("ID", "Sheet1!A1"))

    =ARRAYFORMULA(IF(IMPORTRANGE("ID", "Sheet1!A1:A")="$0", "$50", 
                     IMPORTRANGE("ID", "Sheet1!A1:A")))

  • to avoid #N/A wrap it into IFERROR

| improve this answer | |
  • Sorry to ask such an ignorant question ... what does "ID" represent? The field name? – Joe Manausa Feb 21 '19 at 12:39
  • ID is a number from url. in importrange you have two options: to either use URL like for example: https://docs.google.com/spreadsheets/d/16nnuzNRx_kGGJm1EBVX7kBT2bfW1-2TYwSiuaLF-QIA/edit#gid=187215959 or ID: 16nnuzNRx_kGGJm1EBVX7kBT2bfW1-2TYwSiuaLF-QIA from it (shorter option). it's the unique number from between d/ and /edit – user0 Feb 21 '19 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.