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:
http://igoogledrive.blogspot.com/p/paid-projects.html

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,

10 comments:

  1. UnknownJune 14, 2017 at 8:35 AM

    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
    Replies
    1. DustinekOctober 31, 2018 at 5:45 AM

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

      Delete
  2. UnknownFebruary 15, 2019 at 10:20 PM

    You are missing another countif function.

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

    Try this

    ReplyDelete
  3. UnknownFebruary 15, 2019 at 10:21 PM

    You are missing another countif function.

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

    ReplyDelete
  4. UnknownFebruary 25, 2019 at 11:02 AM

    Try ImportRange first it will ask you permission after that it will work fine

    ReplyDelete
    Replies
    1. AnonymousMay 8, 2019 at 8:20 PM

      Thanks your ans very helpfull to me

      Delete
  5. yusufAugust 25, 2020 at 12:07 PM

    How to count value from other outside Spreadsheets??
    Wait...I am breaking down the problem.
    There is a google sheet named (RM 20.08.2020 Responses) where I collect responses according to reg. no of every student in my class.
    And now I want a student who has (reg. no. 20177310-02) in the (responses) sheet get a value of 1 marks in another google (attendance counter) sheet according to her reg.no.
    The other sheet is here. Link Below.
    Please find me a solution based on COUNTIF and ImportRange. Just find me the formula which works. I will copy-paste them to other cells.
    I have also given the link of these 2 sheets for your ease.
    Many Many Thanks in advance...
    Links (Responses): https://docs.google.com/spreadsheets/d/1mWtCKQggKUoGAn9td5nkjva6UBK5WHtq_NHBd-lEj8c/edit?usp=sharing

    Links (Attendance Counter): https://docs.google.com/spreadsheets/d/1PKk0y1yoi0smAYKfGwMkLSUkJLWtb0JwmWIVKdnOKoY/edit?usp=sharing

    ReplyDelete
  6. UnknownFebruary 9, 2022 at 8:18 AM

    worked for me, thank you so much. I wonder if theres a simpler way of counting a specific cell across all sheets

    ReplyDelete
  7. AnonymousMarch 2, 2022 at 11:58 PM

    Hi I am trying to get a data from another sheet with this formula. Please help me figure it out.

    =countif(ImportRange("https://docs.google.com/spreadsheets/d/1g_-VVW7GTsx08hJZHQRLCKat_nnfbf4AyVtUf47dtwM/edit#gid=1545894456","Appraisal Follow up!I1:I149,"="&DATE(2022,3,1))"))

    I want to count the items in a a specific date from another worksheet sheet

    ReplyDelete

Subscribe to: Post Comments (Atom)