Friday, August 9, 2013

Countif in Different Sheets and Spreadsheets

Question:

( 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,

Solution:


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

=countif('Sheet1'!C2:C50;"Yes")+countif('Sheet2'!C2:C50;"Yes")+countif('Sheet3'!C2:C50;"Yes")


=================


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: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

1 comment:

  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

    =countifs(ImportRange("0AmMTqpzD9YRndE81QjNTcDItUnBqbHFSNWdxLXBNU1E";"Sheet1!C2:C50");"1"),(ImportRange("0AmMTqpzD9YRndE81QjNTcDItUnBqbHFSNWdxLXBNU1E";"Sheet1!D2:D50");"ART")

    Any help would be greatly appreciated
    Niki

    ReplyDelete