Google-sheets – Get the sum of every row in a table imported with Importhtml

google sheetsimporthtml

I've figured out how to import HTML table into Google spreadsheets, but is there a way to sum a row?

I want to get a sum of Total Revenue from Nasdaq.

This allowed me to pull the quarterly data from Nasdaq:

=arrayformula(index(importhtml("http://www.nasdaq.com/symbol/ko/financials?query=income-statement&data=quarterly", "table",0)))

I thought I could sum the entire row by adding this to the end:

sum(c2+d2+e2+f2)))

It doesn't work and I get an error:

=arrayformula(index(importhtml("http://www.nasdaq.com/symbol/ko/financials?query=income-statement&data=quarterly", "table",0),sum(c2+d2+e2+f2)))))

Am I supposed to just find the 4 data and sum them instead of summing them after I pull the data?

Best Answer

I don't see why you have arrayformula and index there; they don't do much (other than index messing up formatting of dates and currency). I don't see what "0" is supposed to do in the importhtml command, either.

In order to get the sum of particular columns, you can use query. 

=query(importhtml("http:...", "table"), "select Col1, Col3+Col4+Col5+Col6 label Col3+Col4+Col5+Col6 'Total'")

(The part label Col3+Col4+Col5+Col6 'Total' is optional, it just makes the header look nicer.)

If you want to also have the quarter data, then include the quarters column too; you may also take the opportunity to rearrange them chronologically

=query(importhtml("http:...", "table"), "select Col1, Col3, Col6, Col5, Col4, Col3+Col4+Col5+Col6 label Col3+Col4+Col5+Col6 'Total'")

Finally, notice that the ending dates of each quarter is not the sort of thing we want to add up. One way to avoid adding them is to use the third parameter of query, the number of headers:

=query(..., "...", 2)

This tells the query that the second row is also a header, so its contents will be added to the header. Another solution is to add a where clause to the query string to exclude this row:

select ... where Col1 != 'Quarter Ending:' label ...
Related Topic