+ Reply to Thread
Results 1 to 3 of 3

COUNTIF and IMPORTRANGE forumla

  1. #1
    Registered User
    Join Date
    02-02-2018
    Location
    Wharton
    MS-Off Ver
    Texas
    Posts
    3

    COUNTIF and IMPORTRANGE forumla

    Seeking wisdom from experts in excel because I'm certainly not. I have a spreadsheet that principals in our district use to do teacher daily walkthroughs. (This spreadsheet is ongoing and will continue throughout the year. This data is populated and pulled into an ongoing spreadsheet of total percentages per domain.) By the grace of God, I have this part working correctly. (Well, maybe I am learning a bit about Excel) What I need, is a formula to take total walkthroughs per week for a specific principal and put in an ongoing table sheet per principal. (See examples)

    Each week, the principals meet with higher administration to go over this document. Each week, (Example 2) should show their weekly walkthrough totals per principal.

    The formula I've put in isn't working ;=COUNTIF(IMPORTRANGE("1icL7PmCVYwqmfFyaVGLHA02ANngrS9lIqgG6kbyyWo","SIV!b2:b300"), "Dana")
    Dana is one of the principals.

    To take this one step further, is it possible for me to go in each week and use the time stamps on example 1 to get a total for each principal walkthrough through a specific date?

    I apologize for how confusing this sounds.
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: COUNTIF and IMPORTRANGE forumla

    IMPORTRange is not an Excel Function. I am moving this thread to Other Platforms as it appears to be a Google Sheets Issue.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: COUNTIF and IMPORTRANGE forumla

    With certainty, Excel only accepts RANGE REFERENCES as 1st argument to COUNTIF. Since Excel doesn't have IMPORTRANGE, you seem to be using Google Sheets. I suspect that Google Sheets's COUNTIF also only accepts range references as 1st argument, and IMPORTRANGE results aren't ranges. Google Sheets's QUERY function appears to require that its 1st argument be a range reference too. However, Google Sheet's DCOUNT appears to be able to use arrays as 1st argument. So, try

    =DCOUNTA(IMPORTRANGE("1icL7PmCVYwqmfFyaVGLHA02ANngrS9lIqgG6kbyyWo","SIV!B2:B300"),2,{"Administrator";"Dana *"})

    You could do a lot more. If you mean given a start of week date in cell D5 in the same worksheet as the formula above, try

    =DCOUNTA(IMPORTRANGE("1icL7PmCVYwqmfFyaVGLHA02ANngrS9lIqgG6kbyyWo","SIV!B2:B300"),2,{"Administrator","Timestamp","Timestamp";"Dana *",">="&D5,"<"&(D5+7)})

    That said, I haven't used Google Sheets enough to know how efficiently it handles many formulas making the same IMPORTRANGE call, but I suspect you'd be FAR BETTER OFF adding a worksheet in the workbook in which you need these formulas, name it ImportedTable, enter =IMPORTRANGE("1icL7PmCVYwqmfFyaVGLHA02ANngrS9lIqgG6kbyyWo","SIV!B2:B300") in that worksheet's A1 cell, and the entire indicated range should appear as a range in that workbook. Then you could use COUNTIF and COUNTIFS.

    =COUNTIF(ImportedTable!$B$2:$B$300,"Dana *")

    and

    =COUNTIFS(ImportedTable!$B$2:$B$300,"Dana *",ImportedTable!$B$2:$B$300,">="&$D$5,ImportedTable!$B$2:$B$300,"<"&($D$5+7))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] correction in countif forumla
    By mazan2010 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-27-2016, 10:24 AM
  2. CountIF forumla
    By Seagood3 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-17-2015, 12:20 PM
  3. Countif Forumla
    By chubbchubb in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-20-2014, 10:50 AM
  4. How do i convert this countif forumla to VBA
    By supersingh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-25-2012, 06:30 AM
  5. Countif Or sumif forumla
    By masond3 in forum Excel General
    Replies: 4
    Last Post: 07-06-2012, 08:00 AM
  6. CountIF Forumla
    By neil25 in forum Excel General
    Replies: 6
    Last Post: 06-17-2008, 08:20 AM
  7. Forumla Help!countif?IF?If(AND? if(OR?
    By harpscardiff in forum Excel General
    Replies: 6
    Last Post: 01-19-2006, 06:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1