## 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.

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")

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