Count function

[Shamz41]

Hi
I need to do count function on the attached file for cell A2 on the "sheet1" tab. the count should indicate the total submissions from Column I "Test File 1" when it is triggered to show on the table.
I've input the suggested results that should be shown.

[bebo021999]

Try:

=COUNTIF('Test File 1'!$H$2:$H$1000,A1)

[Glenn Kennedy]

In A2, copied across:

=COUNTIF('Test File 1'!$H:$H,Sheet1!A$1)

[Shamz41]

Thank you. It worked.

[Shamz41]

Hi
Can I check if I have another criteria in column D called number of submissions. How do I implement the formula to count along the collection dates with the number of submissions?

[Shamz41]

Hi, can i check as the formula is linking to another external file with the data table. It shows #value whenever I close the file with the link. Is there a way to lock it like an array.
[=COUNTIF('Test File 1'!$H:$H,Sheet1!A$1)]

[Phuocam]

Try this...

=SUMPRODUCT(--('Test File 1'!$H:$H=Sheet1!A$1))