Search code examples
google-sheets-formula

sum of importrange from different google sheet links


=sum( IMPORTRANGE(AB2,"Copy of Nutrional Status!c10"), IMPORTRANGE(AB3,"Copy of Nutrional Status!c10"), IMPORTRANGE(AB4,"Copy of Nutrional Status!c10"), IMPORTRANGE(AB5,"Copy of Nutrional Status!c10"), IMPORTRANGE(AB6,"Copy of Nutrional Status!c10"), IMPORTRANGE(AB7,"Copy of Nutrional Status!c10"), IMPORTRANGE(AB8,"Copy of Nutrional Status!c10"), IMPORTRANGE(AB9,"Copy of Nutrional Status!c10"), IMPORTRANGE(AB10,"Copy of Nutrional Status!c10"), IMPORTRANGE(AB11,"Copy of Nutrional Status!c10"), IMPORTRANGE(AB12,"Copy of Nutrional Status!c10"), IMPORTRANGE(AB13,"Copy of Nutrional Status!c10"), IMPORTRANGE(AB14,"Copy of Nutrional Status!c10"), IMPORTRANGE(AB15,"Copy of Nutrional Status!c10"))

how can i shortened this formula?

i've tried =sum( IMPORTRANGE(AB2:AB15,"Copy of Nutrional Status!c10"))

but not all data where cell values where added

note: cells AB2:AB!5 contain the google sheet links

thank you


Solution

  • Use REDUCE

    =REDUCE(,AB2:AB15,LAMBDA(tot,ss,tot+SUM(IMPORTRANGE(ss,"Copy of Nutrional Status!C10"))))