COUNT rows on external sheet if between date range

[charles.middleton]

Hi guys,

I use a number of separate Google Sheets to record applications made through a Google Form.

We have c20 forms, each with it's own sheet.

I'm trying to build a central tracking sheet (in a new book), that can count the number of applications made (rows) between two dates.

For example, using IMPORTRANGE to pull data from the external sheet, take the STARTDATE and ENDDATE from the central reporting sheet, IMPORTRANGE to count all non-blank rows from the external sheet ONLY where the datestamp onthe application is between the STARTDATE and ENDDATE.

I can do this count on the local sheet...

=SUMPRODUCT((A2:A100>=C2)*(A2:A100<D2+1))

(Where C2 is the STARTDATE and D2 is the ENDDATE)...

....but cant make it work when importing the data.

So far I've got

=sumproduct((IMPORTRANGE("1FDObu7x-UJ29BT_7mW8MKur0LiSCEkStkw8D7mr9V1E","Form Responses 1!a:a">=E1)*(IMPORTRANGE("1FDObu7x-UJ29BT_7mW8MKur0LiSCEkStkw8D7mr9V1E","Form Responses 1!a:a"<G1+1))))

....which I wrote based on the local sumproduct above, but no joy.

Any suggestions?

Thanks

 

[Claus Busch]

Hi,

Am Mon, 13 Oct 2014 05:24:56 -0700 (PDT) schrieb
(e-mail address removed):

=SUMPRODUCT((A2:A100>=C2)*(A2:A100<D2+1))

you have to insert the path and the workbook name into the formula.

Modify path and book name to suit:
=COUNTIF([Book1.xlsx]Sheet1!$A:$A,"<="&G1+1)-COUNTIF([Book1.xlsx]Sheet1!$A:$A,"<"&E1)
or
=SUMPRODUCT(--([Book1.xlsx]Sheet1!$A$1:$A$500>=E1),--([Book1.xlsx]Sheet1!$A$1:$A$500<=G1+1))


Regards
Claus B.

 

[Claus Busch]

Am Mon, 13 Oct 2014 14:44:44 +0200 schrieb Claus Busch:

Hi,

Am Mon, 13 Oct 2014 05:24:56 -0700 (PDT) schrieb
(e-mail address removed):

=SUMPRODUCT((A2:A100>=C2)*(A2:A100<D2+1))

you have to insert the path and the workbook name into the formula.

Modify path and book name to suit:
=COUNTIF([Book1.xlsx]Sheet1!$A:$A,"<="&G1+1)-COUNTIF([Book1.xlsx]Sheet1!$A:$A,"<"&E1)
or
=SUMPRODUCT(--([Book1.xlsx]Sheet1!$A$1:$A$500>=E1),--([Book1.xlsx]Sheet1!$A$1:$A$500<=G1+1))

Regards
Claus B.

Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Mon, 13 Oct 2014 14:44:44 +0200 schrieb Claus Busch:

Modify path and book name to suit:
=COUNTIF([Book1.xlsx]Sheet1!$A:$A,"<="&G1+1)-COUNTIF([Book1.xlsx]Sheet1!$A:$A,"<"&E1)
or
=SUMPRODUCT(--([Book1.xlsx]Sheet1!$A$1:$A$500>=E1),--([Book1.xlsx]Sheet1!$A$1:$A$500<=G1+1))

if the formula is not in an external workbook but in another sheet then:
=SUMPRODUCT(--(Sheet1!$A$1:$A$500>=E1),--(Sheet1!$A$1:$A$500<=G1+1))
=COUNTIF(Sheet1!$A:$A,"<="&G1+1)-COUNTIF(Sheet1!$A:$A,"<"&E1)



Regards
Claus B.