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

C	D	E
	6/4/2012	6/6/2012
	Transaction	Average
Review Flow	Formula	Formula

<TBODY>
</TBODY>

Sheet B has the following info

A		J	K
1.01		Review Flow	6/4/2012
22		ABC	6/5/2012
109		DEF	6/12/2012

<TBODY>
</TBODY>

 

[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

A	B	C
	6/4/2012	6/6/2012
	Transaction	Average
Review Flow	=COUNTIFS($D$8:$D$11,">="&B2,$D$8:$D$11,"<="&C2,$C$8:$C$11,A4)	Formula
A	B	C	D
1.01		Review Flow	6/4/2012
22		ABC	6/5/2012
109		DEF	6/12/2012

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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