Formula to count after filtering data

[redspanna]

I have a sheet that contains several thousand rows of data that I will filter to get what I need.

Basically I would like a formula or maybe code that will count the number of times the number '1' is shown through the filtered rows in column K IF the date value through the filtered data in column A is equal to a date that is shown in cell A1 of sheet2.

So If date in A1 sheet2 is 30Aug14, count how many times the number 1 is shown in the same filtered data through column K

Hope this makes sense and thanks in advance

 

[Hercules1946]

Hello Redspanna
Assuming that your wanting to count the instances where the dates match when the string "dep" appears in ANY cell in Col C:

=SUMIF(A:A,Sheet2!$A$1,K:K)* (COUNTIF(C:C, "dep" )>0)

This means that Excel will count all the matching dates, provided that there is at least one instance of the string "dep" (anywhere) in Col C.

HTH

 

[Aladin Akyurek]

Hello Redspanna
Assuming that your wanting to count the instances where the dates match when the string "dep" appears in ANY cell in Col C:

=SUMIF(A:A,Sheet2!$A$1,K:K)* (COUNTIF(C:C, "dep" )>0)

This means that Excel will count all the matching dates, provided that there is at least one instance of the string "dep" (anywhere) in Col C.

HTH

This will multiply a conditional sum result with a conditional count result. Is this what is wanted?

 

[Hercules1946]

This will multiply a conditional sum result with a conditional count result. Is this what is wanted?

Hello Aladin - How r u ?
In answer to your question, Im not sure as the explanation is a little ambiguous.

In post #7 the OP is happy with the SUMIF that sums the contents of col K where the date in the same row in Col A matches the date in Sheet2!A1
However, the OP asks for the following amendment:
"I also only want column K counted if the date is matched AND column C has 'dep' in any cell".
I interprete this as meaning col K will not be counted unless column C has 'dep' in any cell in the column. Based on this, the formula gives the correct result. Because of the ambiguity, I have asked for
clarification, and when this is provided it may change the solution.

 

[Hercules1946]

Redspanna
Did you try my suggestion in post #11?