Criteria Count and Between Dates

[stwp86]

Hey everyone . . .

Got a question about a few formulas I am trying to work through. I am attempting, unsuccessfully, to create a formula that will first look at a range and make sure the selected dates are within range and then count the occurrences of a cells value that matches within a range. I will need to do something similar, but get the average time from a different column in Sheet B for the formula in Sheet A column E.

Ideal state for the formula in D3 is - countif (D1>=SheetB!K:K and E1<=SheetB!K:K and C1=SheetB!J:J)
Ideal state for the formula in E3 is - averageif(D1>=SheetB!K:K and E1<=SheetB!K:K and C1=SheetB!J:J) using SheetB A:A as the average column.

Any and all help is greatly appreciated.

If possible I would like to keep this out of VBA.

Thanks!

Sheet A has the following info
[TABLE="width: 500"]
<TBODY>[TR]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6/4/2012
[/TD]
[TD]6/6/2012
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Transaction
[/TD]
[TD]Average
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Review Flow
[/TD]
[TD]Formula
[/TD]
[TD]Formula
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


Sheet B has the following info
[TABLE="width: 500"]
<TBODY>[TR]
[TD]A
[/TD]
[TD][/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[/TR]
[TR]
[TD]1.01
[/TD]
[TD][/TD]
[TD]Review Flow
[/TD]
[TD]6/4/2012
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD][/TD]
[TD]ABC
[/TD]
[TD]6/5/2012
[/TD]
[/TR]
[TR]
[TD]109
[/TD]
[TD][/TD]
[TD]DEF
[/TD]
[TD]6/12/2012
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Excell_Wannabe]

Have you tried countifs?

This example has some formatting errors, but gets the point across

 

[stwp86]

Tried the following:
=COUNTIFS(Data!J:J,"="&'Data Review'!$C9,Data!K:K,">="&'Data Review'!$D$1,Data!K:K,"<="&'Data Review'!$E$1)

Am recieving 0 when I should be getting 138

 

[Excell_Wannabe]

Tried the following:
=COUNTIFS(Data!J:J,"="&'Data Review'!$C9,Data!K:K,">="&'Data Review'!$D$1,Data!K:K,"<="&'Data Review'!$E$1)

Am recieving 0 when I should be getting 138

[TABLE="class: grid, width: 390"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]6/4/2012[/TD]
[TD="align: right"]6/6/2012[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Transaction[/TD]
[TD]Average[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Review Flow[/TD]
[TD]=COUNTIFS($D$8:$D$11,">="&B2,$D$8:$D$11,"<="&C2,$C$8:$C$11,A4)[/TD]
[TD]Formula[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]1.01[/TD]
[TD] [/TD]
[TD]Review Flow[/TD]
[TD="align: right"]6/4/2012[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD] [/TD]
[TD]ABC[/TD]
[TD="align: right"]6/5/2012[/TD]
[/TR]
[TR]
[TD="align: right"]109[/TD]
[TD] [/TD]
[TD]DEF[/TD]
[TD="align: right"]6/12/2012[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]

[/TD]
[/TR]
</tbody>[/TABLE]

I might be misunderstanding what you want it to do, but this appears to work. I dont think you needed the = data review