# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  >  COUNTIF and IMPORTRANGE forumla

## hroddy

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.

----------


## alansidman

IMPORTRange is not an Excel Function.  I am moving this thread to Other Platforms as it appears to be a Google Sheets Issue.

----------


## hrlngrv

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

----------

