Friday, August 9, 2013

Countif in Different Sheets and Spreadsheets


( by Alexandru Danciu )

I have to count the YES elements from various spreadsheets, say A, B, C, D, E, into a TOTAL spreadsheet. 

Each spreadsheet has per column values for Yes or NO. For example, spreadsheet A has Yes and NO answers from C2 to C50.

In excel it works perfectly with COUNTIFS, however gdocs gives me a bit of a headache. Please help :)

Thank you so much,


To count "Yes" in various sheets (tabs) in same Spreadsheet use the following formula:



To count "Yes" in various Spreadsheets you will need to use function ImportRange.

Try the following formula:

=countif(ImportRange("0AmMTqpzD9YRndE81QjNTcDItUnBqbHFSNWdxLXBNU1E";"Sheet1!C2:C50");"Yes") + countif(ImportRange("0AmMTqpzD9YRndHprbmpCajRJbzQtZzdVZmEyM0JTV3c";"Sheet1!C2:C50");"Yes")

Replace: "0AmMTqpzD9YRndE81QjNTcDItUnBqbHFSNWdxLXBNU1E" and "0AmMTqpzD9YRndHprbmpCajRJbzQtZzdVZmEyM0JTV3c" with your spreadsheet keys that you can find in the URL of your Spreadsheet.

And replace "Sheet1!C2:C50" with the sheet name and range of which you want to count the criteria "Yes".

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: 



  1. Hi
    Following on from the COUNTIF answer, I am trying to apply it to a COUNTIFS formula but seem to be getting an error


    Any help would be greatly appreciated

    1. did you figure this out? i'm looking for solution as well